The crew at 4 Guys From Rolla have come through for me again (as they frequently do in matters of ASP, .NET and SQL). In building a new webservice, I wanted to be able to pass in a comma-delimited list of ID's to a SQL stored procedure. Unfortunately, there isn't a SQL datatype for something like "comma delimited list". I could have taken the "easy" way out and built the SQL query dynamically in C# instead of calling a stored procedure, but then I would have been mad at myself for breaking the rule that we agreed on that all the webservices would strictly call stored procedures. I figured I'd have to use SQL to parse a passed in string into ID's, and build the SQL query from there. However, this seemed like something someone else probably would have done in the past. I found a few techniques online but this one seemed the cleanest and best-implemented. You basically pass in a big varchar string of id's into the stored procedure and then call a SQL User Defined Function called "Split" which returns a table of the ID's which you can join to. It's simpler than it sounds...instead of having something like
SELECT * FROM table WHERE id in (--lots of convoluted code to parse out the ids--)
you just call
SELECT * FROM table WHERE id in ( SELECT convert(int,Value) FROM dbo.Split(@list_string,',') )
The complete code for the function and examples of how to use it are listed in the article. Thanks to "4 Guys" for saving me a couple hours and some headaches with this nifty technique!
Submitted by Jared (not verified) on June 7, 2007 - 12:29pm.
Thanks for linking this up. The Split() technique is easy to implement without adding a whole lot of extra code.
Submitted by Cris (not verified) on October 10, 2007 - 9:54am.
Thanks SO MUCH for this. I never would have come up with this most eloquent solution. And shame on SQL Server for requiring it. Love to 4 Guys From Rolla.
Submitted by Siddus Vious (not verified) on November 9, 2007 - 9:52am.
Wow! You and the 4 guys should get some sort of an award for this! This has made life so much more stress free!
Submitted by Mike Bosch (not verified) on December 30, 2007 - 7:45am.
I wonder how well this would scale? I would imagine it is better to first create a table variable in the local procedure and then use that table in the actual WHERE clause? Otherwise, wouldn't the function have to be evaluated (and re-split) for each row in the table?
Submitted by Athadu (not verified) on March 12, 2009 - 6:29pm.
Trying to use this for a char based values as in:
SELECT convert(varchar(100), Value) FROM dbo.Split('''ListItem1'', ''ListItem2''',',')
doesn't seem to work.
Eventhough the above statement by itself works and returns back a temp table with two 'listitems' - when used within another select statement with the IN clause doesn't seem to work.
Any ideas why?
Thank you
Athadu
Submitted by Athadu (not verified) on March 12, 2009 - 6:31pm.
My mistake...
SELECT convert(varchar(100), Value) FROM dbo.Split('ListItem1, ListItem2',',')
works perfectly - i was trying to be too smart and put in too many quotes.
Thank you for the excellent solution.
Regards
Athaud
Submitted by Sam (not verified) on June 17, 2009 - 5:02pm.
Hey it worked!!! even with GUIDs!!! i had a function that takes the comma delimited and convert it to a temp table then i would JOIN the function to my query. this worked fine for numbers and guid IN SQL -- although i modified the function to work with GUIDs( parse also '{' and '}' and ',') it worked fine even with guids in sql but i get a cast error(string to guid) in my application although im getting the correct result set in sql and are passing the right paramater types(string/nvarchar). A next alternative i was thinking of was to create a temp table and populate it with the guids(im getting the guids from a chklistbox) maybe call a looped insert statement to populate it then use the temp table in my query either JOIN it or 'Select IN(Select Guids from @temptable)' but i wont have to go thru all of this!!! thanks to this post :) much appreciated!!!
Submitted by Asif Hafeez (not verified) on October 13, 2009 - 1:36am.
Thanks to "4 Guys From Rolla". Excellent solution provided.
Submitted by superservo15 (not verified) on May 18, 2010 - 4:06pm.
Exactly was I was looking for, thanks for the link up! I
Submitted by online marketing (not verified) on April 11, 2011 - 5:23am.
Thank you very much.
This is of great help, as we have to write lesser code and will save our precious time.
online marketing
Submitted by Somerset Locksmiths (not verified) on July 29, 2011 - 3:59am.
Terrific work ! Your site has presented me much of the particulars I wished .
Somerset Locksmiths
Submitted by admission essay (not verified) on August 17, 2011 - 4:50am.
The hardest part about applying to graduate school is writing the admissions essay, also known as the personal statement. Learn how to improve your essay.
Submitted by louettaoneal (not verified) on August 23, 2011 - 8:52am.
Thanks for linking this up. The Split() technique is easy to implement without adding a whole lot of extra code.
cafetières
Submitted by willi (not verified) on September 26, 2011 - 7:36am.
However, the main issues of portability SQL RDBMS products there is still a lack of comprehensive interpretations, or different standard. Among these reasons are great, and the incomplete definition of standards and a single supplier.
Submitted by Steve (not verified) on November 10, 2011 - 11:52am.
I almost gave up on a solution for updating multiple rows from one call to the database. This solved my problem ;-)
Submitted by silver price per ounce (not verified) on December 27, 2011 - 4:26am.
There are very interesting points in time there in article but I don’t know if I see all of them heart to middle . There is some validity but I will take hold judgement until I look into it further. Good article , thanks and we want more..
Submitted by gold and silver prices live (not verified) on January 3, 2012 - 1:03am.
It's really great stuff overall, I just wanted to say thank you. I am looking forward to another great article from your side.
Submitted by gold prices today (not verified) on January 9, 2012 - 12:43am.
There are very informative stuff which I looking for, I'll bookmark this site for further information, such a great job.
Submitted by current gold prices (not verified) on January 11, 2012 - 6:10am.
I really like it! I'll always appreciate your brief sharing in this awesome stuffs sincerely, this discussion has put light on this topic.
Submitted by modular kitchen in india (not verified) on January 30, 2012 - 6:23am.
It's really great stuff and I look forward to reading your post. What you have to say is important and should be read.
Post new comment