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

x
?
Solved

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

Posted on 2013-05-28
5
Medium Priority
?
392 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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