[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Split Strings into New Rows based on multiple delimiters

Posted on 2013-01-08
2
Medium Priority
?
900 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

829 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