CODEWITHSUNDEEP
sql
BillB
BillB

Reputation: 11

SQL Replace Commas in the a row of a table

I have table test( ID Numeric(11,0), report varchar(255) ) and data looks below 

1   ,Age,,,,,,family_status,,,,,,
2   ,,,,,,,,retaliation,hostile environment,,,,
3   ,,,,,,,,,,,,,
4   ,,,,,,,,retaliation,,,,,
5   ,,,,,,,,,hostile environment,,,,
6   ,Age,,,,,,,,,,,,
7   ,,,,national_origin,,,,,,,,,
8   Sex,,,,,,,,,,,,,
9   ,,,,national_origin,,disability,,retaliation,,,,,
10  Sex,,,,,,,,retaliation,,,,,
11  ,,,,,,,,

and i would like to update this table by replacing or using any other data scribing to remove extra commas so that data looks 

1   Age,family_status
2   retaliation,hostile environment
3   
4   retaliation
5   hostile environment
6   Age
7   national_origin,
8   Sex
9   national_origin,disability,retaliation
10  Sex,retaliation
11

i try to use the below statement but not sure how to loop through so that it will check and remove all the commas 

UPDATE table test SET report = replace(report , ',,', ',')

Upvotes: 1

Views: 5036

Answers (1)

JohnFx
JohnFx

Reputation: 34909

If you are just doing this as a one off task (rather than a scripted process you expect to use repeatedly) you could always just run this query repeatedly until you get 0 rows updated

UPDATE table test SET report = replace(report , ',,', ',')
WHERE report like '%,,%'

If you need to do this over and over, or put it in a program I recommend using your procedural (non SQL code) to do the replace where you have better text manipulation commands.

If you aren't thrilled with that, check out this blog article I wrote on a similar problem of replacing repeating spaces from a string.

Upvotes: 2

Related Questions