CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Jeff Puckett
Jeff Puckett

Reputation: 40841

SQL removing extra commas in middle of string

I have data like this ,,a,,c,,,,,i,j,k,,m,,,,,
I need to remove the extra commas so it looks like this a,c,i,j,k,m

I am using MS SQL Server 2012

Upvotes: 2

Views: 3366

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

There is a cool trick for this:

select replace(replace(replace(col, ',', '><'), '<>', ''), '><', ',')

This gets rid of all duplicate commas, but not the ones at the beginning and end. If you have no spaces in the values, I'm going to recommend replacing the commas with spaces and then back again, so you can use the trim functions:

select replace(ltrim(rtrim(replace(replace(replace(replace(col, ',', '><'), '<>', ''), '><', ','), ',', ' '))), ' ', ',')

I'm not sure if there is a prettier method, unless you define your own function.

Upvotes: 8

Related Questions