I have a field in a sql7 db. The data in the field may look like this:
1007;1000;10013
I need to remove some data from the field but not all of it. I need to remove
1000; and replace it with null. Any idea?
Printable View
I have a field in a sql7 db. The data in the field may look like this:
1007;1000;10013
I need to remove some data from the field but not all of it. I need to remove
1000; and replace it with null. Any idea?
"Dennis" <dtucker01@hotmail.com> wrote:
>
>I have a field in a sql7 db. The data in the field may look like this:
>1007;1000;10013
>
>I need to remove some data from the field but not all of it. I need to remove
>1000; and replace it with null. Any idea?
Hi,
Update TableName Set FieldName = REPLACE(FieldName, '1000;', '')
Where FieldName Like '%1000;%'
All occurences of '1000;' will be replace with a nullstring which means removed
from the string.
Look up the REPLACE-function in BOL.
Bernie
Write a quick little VB app that selects the PK from the table and the field
your needing to modify where the field needing to modify contains the 1000;
Using the VB app, go through each record do a replace(fxn) on the record
field and then peform the update.
so something like...
dim con as connection
dim rs as recordset
DIm SQL as string
set con = getgonnection() 'Get connection is fxn that establishes connection
to db
SQL = "SELECT PK, Field from tblName where Instr(Field,'1000;')>0"
rs.open (SQL,CON)
While not rs.eof
SQL = "Update tblName set Field = '" & Replace(rs.fields("Field"),"1000;","NULL;")
& "' WHERE PK=" & rs.fields("PK")
conn.execute(SQL)
wend
"Dennis" <dtucker01@hotmail.com> wrote:
>
>I have a field in a sql7 db. The data in the field may look like this:
>1007;1000;10013
>
>I need to remove some data from the field but not all of it. I need to remove
>1000; and replace it with null. Any idea?
