CODEWITHSUNDEEP
sqlsql-server-2005
David
David

Reputation: 51

Replacing the first 2 strings of similar characters in the column

I want to replace the first 2 characters of all entries that starts with "01" with "A0".

table = Seq

Column1
01010101
01010102
01010103

result should be:

Column1
A0010101
A0010102
A0010103

I can't use the replace because I don't want to change the succeeding "01".

Update [Seq]
Set Column1 = Replace(Column1, '01', 'A0')
Where Column1 like '01%'

I'm hoping to use Stuff instead, but I can't seem to make it work. 

Update [Seq]
Set Column1 = 'A0' + substring(Column1, 2, len(Column1)-6)
where Column1 like '01%'

Any help is appreciated.

Upvotes: 1

Views: 37

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

Try this:

Update [Seq]
Set Column1 = 'A0' + RIGHT(Column1, LEN(Column1) - 2)
where Column1 like '01%'

Demo here

Using STUFF:

Update [Seq]
Set Column1 = STUFF(Column1, 1, 2, 'A0') 
where Column1 like '01%'

Upvotes: 3

Related Questions