Replacing a string.

[Dennis]

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?

[Bernie]

"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

[Q*bert]

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?