Link to home
Start Free TrialLog in
Avatar of Steve A
Steve AFlag for United States of America

asked on

Split string into 3 separate fields

Hello,
I would like to split a string into 3 different columns that are separated by a comma in the string.
Like - 'Building 1, Chamber A, Slot X1'

The 3 columns would be for the output:
building - 'Building 1'
chamber - 'Chamber A'
slot - 'Slot X1'

Using Sql Server 2008

Thanks
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Eyeballeth thy article on how to pull this off --> T-SQL:  Normalized data to a single comma delineated string and back, scrolling halfway down to 'Comma delineated string to normalized data'.
Avatar of Ovidio Pozo
Ovidio Pozo

You can try this:
select string, 
       LEFT(string,first-1)building,
       SUBSTRING(string,first+1,second-first) chamber,
       RIGHT(string,third-second) slot
from
(
select CHARINDEX(',',string) first,
       CHARINDEX(',',string,CHARINDEX(',',string)+1) second,
       LEN(string)third,
       string
 from
 
 (select 'Building 1, Chamber A, Slot X1' string)a
 
 )b

Open in new window


replace the "(..)a" with the actual query and name the field "string".

You may need a defensive WHERE since data might not follow always the same pattern.

You may also add some trimming to remove spaces.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can use rtrim or get rid of extra spaces after comma by

replace(@str, ', ',',')

declare @str varchar(100)= 'Building 1, Chamber A, Slot X1';

select id, rtrim(ltrim(data)) data from dbo.splitX(@str,',')
select * from dbo.splitX(replace(@str, ', ',','),',')

ID	Data
1	Building 1
2	Chamber A
3	Slot X1

Open in new window

Avatar of Steve A

ASKER

Nice!  I got this to work and thank you very much.
Thanks to all for the replies as well.