Comma Data Cleanup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comma Data Cleanup

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-10-30 : 11:37:48

How can I get rid of comma's if not part of a "valid" value?
For instance, I have a 'Sample' table that contains a 'State' column
The values in the column could be empty, contain states separated by a comma, or contain one or more single commas. I want to remove those, while keeping states with a comma separating them.
Thoughts...?

CURRENT
TABLE = 'Sample'
COLUMN = 'State'


State
Alabama
, 
Alaska, Arizona
California, Colorado
empty string
, 
empty string
, , 
Deleware, Georgia, Utah

DESIRED RESULTS


State
Alabama
empty string 
Alaska, Arizona
California, Colorado
empty string 
empty string 
Deleware, Georgia, Utah

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-30 : 11:47:54

[code]DELETE FROM mytable WHERE LTRIM(REPLACE(State, ',', '')) = ''[/code]

Note this may not be too fast
djj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 12:48:00

Why delete? As per OPs required output it should be just this


UPDATE Sample
SET State = ''
WHERE LEN(LTRIM(REPLACE(State,',','')))=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-10-30 : 13:37:27

Thanks to you both...., I ended up using the update solutions which did the trick.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-30 : 14:59:16

Sorry I thought from the output that the blank lines were removed.

djj

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 02:42:10