Solved

Split Strings into New Rows based on multiple delimiters

Posted on 2013-01-08
2
757 Views
Last Modified: 2013-09-28
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,'&','&amp;'), ';', '</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
0
Comment
Question by:Apostle07
  • 2
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38757479
try this

declare @T table (ID int, Col varchar(100))
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;'), ';', '</r><r>')+'</r>' 
as xml ) where Col like '%;%'
union all 
select cast('<r>'+replace(replace(Col,'&','&amp;'), ',', '</r><r>')+'</r>' 
as xml ) where Col like '%,%' ) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
  

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 38757500
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

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now