?
Solved

Split an SQL delimited field and write out a new record for each split found

Posted on 2013-05-28
5
Medium Priority
?
390 Views
Last Modified: 2013-06-07
Hello,

Using Microsoft SQL 2008:

I have an SQL function that takes a string and splits it up into multiple values based on a delimiter.  The function works like:

SplitString(delimiter,stringtosplit)

Here is an example:

SplitString(',','value1,value2,value3')

The output is a table of values:

value1
value2
value3

I would like to cycle through another table and split up a field in the table using the above function, and output a record for every split the function returns.  For instance, if table1 contained the following records:


field1                                 field2
1                                        'test1,test2,test3'
2                                        'test1,test5'

Then I would like to the following from this record:

field1                                 field2

1                                         test1
1                                         test2
1                                         test3
2                                         test1
2                                         test5

Is there a simple way to do this using a with or some other method?

Regards,

Randy.
0
Comment
Question by:Randyg1414
[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
5 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39202525
Are you trying to create records, or display the possible records in a  query?
0
 

Author Comment

by:Randyg1414
ID: 39202539
Create records so I can query on them.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39202865
As you already have the SplitString function, why don't you use it?

SELECT t1.field1,t2.field2
  FROM your_table t1
  CROSS APPLY SplitString(',',field2) t2
0
 

Author Comment

by:Randyg1414
ID: 39202969
That is exactly what I wanted to do.  I didn't realize how powerfull CROSS APPLY is.  Thank you so much
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39202989
Use the 2-part name for the function if you get any error.
SELECT t1.field1,t2.field2
  FROM your_table t1
  CROSS APPLY dbo.SplitString(',',field2) t2

Open in new window

0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 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