Replacing First Occurrence Of Character With One Character And The Second With Another Character In Sql

I have a table with a column Name that can contain substring surrounding with quotations ...'substring'... For each row in this table I need to replace first occurrence of ' with

Solution 1:

First an example of how the syntax works

DECLARE@avarchar(max) ='fgh"abc"sdf'SELECT 
  stuff(stuff(@a, charindex('"', @a),1, '«'), 
    charindex('"', @a, charindex('"', @a) +1), 1, '»')

Result:

fgh«abc»sdf 

This is the query needed where col is the name of your column:

SELECT 
  stuff(stuff(col, charindex('"', col),1, '«'), 
    charindex('"', col, charindex('"', col) +1), 1, '»')
FROM yourtable

Solution 2:

You can do something like :

DECLARE@TmpVarVARCHAR(100)
SET@TmpVar='haha"substring"hehe'SET@TmpVar= STUFF(@TmpVar, CHARINDEX('"', @TmpVar), 1, '«')
SET@TmpVar= REPLACE(@TmpVar, '"', '»')