CODEWITHSUNDEEP
sqlsql-servert-sqlreplacesubstring
ComfortablyNumb
ComfortablyNumb

Reputation: 1456

TSQL Replace Using Substring Replaces Other Parts of String As Well

I have a table made up of one column that has a 100 character string in each row. A second column was added to house the result. I needed to amend certain fix position elements and planned to do the following:

UPDATE myData
SET newData =  REPLACE(oldData,SUBSTRING(eftnwsfull, 16,2),'OC')

The element at position 16,2 is '17'. But, if there are other parts of the string (not at position 16,2) that happen to be '17' are getting changed to 'OC' as well.

I'm baffled to understand how this can happen as I'm specifying the exact position of where to make the replacement. What am I doing wrong?

Upvotes: 2

Views: 541

Answers (2)

Ram
Ram

Reputation: 3091

Try STUFF

UPDATE myData
SET newData = STUFF(oldData, 16, 2, 'OC')

Upvotes: 3

Tak
Tak

Reputation: 1562

Here are a couple of ways (please test as the offsets may be one off) ..

SET newdata = SUBSTRING(oldData, 1, 15) + 'OC' + SUBSTRING(oldData, 18, LEN(oldData) - 17)

or

SET newdata = LEFT(oldData, 15) + 'OC' + RIGHT(oldData, LEN(oldData) - 17)

Upvotes: 0

Related Questions