Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Split Strings into New Rows based on multiple delimiters

Posted on 2013-01-08
2
Medium Priority
?
880 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 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