Steve A
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
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
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'.
You can try this:
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can use rtrim or get rid of extra spaces after comma by
replace(@str, ', ',',')
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
ASKER
Nice! I got this to work and thank you very much.
Thanks to all for the replies as well.
Thanks to all for the replies as well.