Split Strings into New Rows based on multiple delimiters
Posted on 2013-01-08
I'm hoping someone will be able to help me out here.
I'm trying to develop a SQL query which will split strings from a column (ColumnA) into 2 or 3 rows depending on multiple delimiters (i.e. '/' '\' ';') from data that already exists in a table (i.e. TableA). One thing to mention, Creating a function is not an option as I do not have create permissions.
I gave it a shot however I'm only able to hard code the values in my script below and can only split by one delimiter.
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello; John; Smith')
insert into @T values (2, 'xxx ; yyy ; zzz')
from @T as T
cross apply (select cast('<r>'+replace(replace(Col,'&','&'), ';', '</r><r>')+'</r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes('r') as n(r)
One last thing to add as well. For the string that was split, I want to make sure that there are no white space in the beginning of the string or the end.
I'm sure there is a better way to do this. I appreciate the help and time spent on this.