Solved

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

Posted on 2013-05-28
5
385 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 500 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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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