asked on

Split Strings into New Rows based on multiple delimiters

Hi Everyone,

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

select
T.ID,
n.r.value('.', 'varchar(50)')
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.

Thank you.

Apostle07

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

appari

and to remove hardcoding delimiters, use another table with all the delimiters you want as follows:

declare @T table (ID int, Col varchar(100))
declare @delim table (lim varchar(100))
insert into @delim 
select ';' 
union 
select ','

insert into @T values (1, 'Hello; John; Smith')
insert into @T values (2, 'xxx , yyy , zzz')
insert into @T values (2, 'xxx1 , yyy - zzz')
insert into @T values (2, 'xxx1 - yyy - zzz')


select 
  T.ID,
  ltrim(rtrim(n.r.value('.', 'varchar(50)')))
from @T as T
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), lim , '</r><r>')+'</r>' 
as xml ) from @delim where Col like '%' + lim + '%'
) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)

Open in new window