CODEWITHSUNDEEP
sqlsql-servert-sql
Sri
Sri

Reputation: 2273

Remove single quote before and after the comma

I would like to remove the brackets, single quote before and after the comma from a variable in a elegant way in SQL

DECLARE @str NVARCHAR(MAX);

SET @str = N'(''202102'',''202104'',''202105'',''202106'',''202107'')'

Expected output

'202102,202104,202105,202106,202107'

I have tried and managed to remove the brackets but couldn't progress further for the single quotes.

SELECT SUBSTRING(@str, 2, LEN(@str) - 2); -- '202102','202104','202105','202106','202107'

Also tried the following which removes the whole lot after the first value.

SELECT LEFT(@str, CHARINDEX(',', @str) - 1)  -- '202102'

Upvotes: 0

Views: 592

Answers (2)

Rajat
Rajat

Reputation: 5803

Try this char(39) based approach

replace(replace(replace(@str, char(39), ''),'(',char(39)),')',char(39))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think this does what you want:

select replace(replace(replace(str, ''',''', ','), '(''', ''), ''')', '')

Here is a db<>fiddle.

Upvotes: 4

Related Questions