CODEWITHSUNDEEP
sqlsql-serverregex
alexxjk
alexxjk

Reputation: 1711

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 « and second one with » Each row can contain only one quoted substring.

So if I have a record like this 'fgh"abc"sdf' I want to get this 'fgh«abc»sdf'

I can't come up with a solution for this.

Upvotes: 0

Views: 3582

Answers (2)

Kartic
Kartic

Reputation: 2985

You can do something like :

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

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

First an example of how the syntax works

DECLARE @a varchar(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

Upvotes: 2

Related Questions