Solved

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

Posted on 2013-05-28
5
378 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
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

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

Author Comment

by:Randyg1414
Comment Utility
Create records so I can query on them.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
That is exactly what I wanted to do.  I didn't realize how powerfull CROSS APPLY is.  Thank you so much
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now