Update question

[Frank2]

I have a table in which one column contains a string. I want to replace the first x characters of that column with another string where the first x characters match another string.

I think I should use the LEFT function to test for the left most characters I want and REPLACE function.

What I need to know is how do I do that in a SELECT statement?

for example

TableA
MyCol ColX
----- -----
abctest test1
testabc test2
abchere test3

I need to replace "abc" only where it is the first 3 characters of the string with "NewStuff".

thanks for the help

[rmiao]

Try this in sql server:

select * from tablea where substring(mycol, 1, x) = 'your string'

[Frank2]

I'm new at this... Tat select will identify all the strings having the correct prefex. Where do i put the Update to change the string from 'abc' to 'NewStuff'?

[nosepicker]

UPDATE TableA
SET MyCol = STUFF(MyCol, 1, 3, 'NewStuff')
WHERE MyCol LIKE 'abc%'

As always, make a copy of the table before doing an update like this.

I think using STUFF is safer than REPLACE in this instance because if you had a string like this:

'abcwhateverabcdef'

then using REPLACE on 'abc' would give you this result:

'NewStuffwhateverNewStuffdef'

Also, I tried to avoid performing LEFT, SUBSTRING, or any other function on MyCol because that would make the query slower.

[Frank2]

OK got it. and thanks for the advice on using STUFF and what to avoid.