CODEWITHSUNDEEP
sqlsql-server
Daniel
Daniel

Reputation: 35704

cleaning CSV String/varchar in SQL

I've got some quirks in a varchar field that is designated to be used as a CSV (comma separated values) that I'd like to clean up.

for example extra comma: val1, ,val2, val3 or a trailing comma val1, val2,

how do I craft a SQL that replaces , , with , and removes the last character if it is a , ?

Upvotes: 0

Views: 288

Answers (3)

Daniel
Daniel

Reputation: 35704

ended up using a like selector and replace

-- clean tags, remove empty tags
Update [myTable]
set [taglist] = REPLACE([taglist], ', ,', ', ')
  WHERE [taglist] like '%, ,%' -- not really needed, I know

  -- remove last comma
Update [myTable]
set [taglist] = SUBSTRING([taglist], 0, LEN([taglist]))
  WHERE [taglist] like '%,'

Upvotes: 0

hotfix
hotfix

Reputation: 3396

You can use regexp_replace function if you use oracle db

select  REGEXP_REPLACE(REGEXP_REPLACE('val1, ,val2, val3,', ',\s,', ','),'[,]$','')
from dual

in MySql you can write you own function like in Rasika's Blog

Upvotes: 2

John Woo
John Woo

Reputation: 263803

SELECT REPLACE(columnName, ', ,', ', ')
FROM   TableName

this may not work always especially when you have multiple spaces between comma.

Upvotes: 1

Related Questions