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

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.
Randyg1414Asked:
Who is Participating?
 
SharathData EngineerCommented:
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
 
jerryb30Commented:
Are you trying to create records, or display the possible records in a  query?
0
 
Randyg1414Author Commented:
Create records so I can query on them.
0
 
SharathData EngineerCommented:
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
 
Randyg1414Author Commented:
That is exactly what I wanted to do.  I didn't realize how powerfull CROSS APPLY is.  Thank you so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.