CODEWITHSUNDEEP
sqlsql-server
Vicky
Vicky

Reputation: 1338

Replacing all occurrences of a substring in a string in an SQL column except first occurrence

I have a string in a a column text as like below : 

Test:HJ,BHS,Test:FG,SKL,Test:KL,PDC

( and so on ...there is no fixed length of this string )

each time Test appears it is followed by Two letters,Three letters ( this is the patter of the string )

Now I want to Replace this string with Test appearing only once like below:

Test:HJ,BHS,:FG,SKL,:KL,PDC

Upvotes: 0

Views: 1023

Answers (1)

TriV
TriV

Reputation: 5148

You could use replace like this

DECLARE @Text varchar(500) = 'Test:HJ,BHS,Test:FG,SKL,Test:KL,PDC  ( and so on ...there is no fixed length of this string )'

SELECT Replace(@Text, ',' + LEFT(@Text,charindex(':', @Text)), ',:')

Returns

Test:HJ,BHS,:FG,SKL,:KL,PDC  ( and so on ...there is no fixed length of this string )

Upvotes: 2

Related Questions