CODEWITHSUNDEEP
sql-server
user176047
user176047

Reputation: 371

stripping strings between a comma in sql server

I know to do parts of it but not all of it, lets say my table name is REV and column name is DESCR and it has a value like 

R&B , Semiprivate 2 Beds , Medical/Surgical/GYN

i use 

SELECT  DESCR, LEFT(DESCR, Charindex(',', DESCR)),  SUBSTRING(DESCR, CHARINDEX(',', DESCR) + 1, LEN(DESCR)) from REV

i get 'R&B ,' in one column and 'Semiprivate 2 Beds , Medical/Surgical/GYN' in another column in the above select statement but i dont know how to selesct the strings from teh second comma onwards

what i like to return is 'R&B' in one column without the comma and 'Semiprivate 2 Beds' in another column and 'Medical/Surgical/GYN' so on

basically select test between commas and when there is no comma it should be blank

Upvotes: 1

Views: 8144

Answers (2)

jpw
jpw

Reputation: 44871

This should work:

SELECT 
  LEFT(DESCR, CHARINDEX(',', DESCR)-1), 
  SUBSTRING(DESCR, CHARINDEX(',', DESCR)+1, LEN(DESCR)-CHARINDEX(',', DESCR)-CHARINDEX(',',REVERSE(DESCR ))),
  RIGHT(DESCR, CHARINDEX(',', REVERSE(DESCR))-1)  
FROM REV

Sample SQL Fiddle

This will split the string, but leave blank at the beginning and end of the strings, you can use LTRIMand RTRIMto trim away the blanks.

There might be better ways to do this though; see the article Split strings the right way – or the next best way by Aaron Bertrand at (that Andrew mentioned in a comment).

Upvotes: 1

Adel Sal
Adel Sal

Reputation: 718

This should work:

  SELECT 
  LEFT(DESCR, CHARINDEX(',', DESCR)-1), 
  SUBSTRING(DESCR, CHARINDEX(',', DESCR)+1, CHARINDEX(',', DESCR, CHARINDEX(',', DESCR)+1) -  CHARINDEX(',', DESCR) -1 ),
  RIGHT(DESCR, CHARINDEX(',', REVERSE(DESCR))-1)
  FROM REV

Upvotes: 2

Related Questions