stripping strings between a comma in sql server

Question

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

sqlperformance.com/2012/07/t-sql-queries/split-strings - This is a very common kind of question and Aaron Bertrand has published about this multiple times. — Andrew
– Andrew, Commented Mar 29, 2014 at 14:52

Adel Sal · Accepted Answer · 2014-03-29 21:55:30Z

2

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

answered Mar 29, 2014 at 21:55

Adel Sal

7185 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jpw · Accepted Answer · 2014-03-29 22:27:25Z

1

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).

edited Mar 29, 2014 at 22:27

answered Mar 29, 2014 at 14:59

jpw

45k6 gold badges71 silver badges90 bronze badges

2 Comments

user176047 Over a year ago

The 1st and 3rd column in the select works but the middle column is not extracting it correctly it seems to be breaking. e.g: 'Hospital, Swing Beds, Interim-First Claim' comes out as 'Hospital' and 'Swing beds, Interi' and 'Interim-First Claim'. the second column is not coming out correclty

jpw Over a year ago

@user176047 I made some changes and added a sample SQL Fiddle. It should be more correct now, but it will only handle a string with two commas and split it into three parts.

Collectives™ on Stack Overflow

stripping strings between a comma in sql server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related