|
Parse A Comma Delimited String Function
|
Email
|
| Submitted on: |
11/14/2006 12:52:39 PM |
| By: |
Dave Vroman
|
| Level: |
Beginner |
| User Rating: |
By 6 Users |
| Compatibility: |
SQL Server 2000 |
| Views: |
24015 |
|
|
|
|
|
This Routine Returns All Of The Data From A Comma Delimited String Into A Table.
A Simple Test Is:
SELECT * FROM dbo.ParseByComma('1,2,3,4,5,6,7,8')
|
| |
Terms of Agreement:
By using this article, you agree to the following terms...
- You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
- You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
- You may link to this article from another website, but ONLY if it is not wrapped in a frame.
- You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
|
/*********************************************************
** Parse A Comma Delimited String Into A Table
** Description: When A Web Page Sends An Array Of Data To
** The Server, The Array Is Comma Delimited.
** This Routine Returns All Of The Data From A Comma
** Delimited String Into A Table.
**
** By: Dave Vroman
*********************************************************/
CREATE FUNCTION dbo.ParseByComma ( @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
VarSubString VARCHAR(10)
)
AS
BEGIN
DECLARE @intPos INT,
@SubStr VARCHAR(10)
-- Remove All Spaces
SET @String = REPLACE(@String, ' ','')
-- Find The First Comma
SET @IntPos = CHARINDEX(',', @String)
-- Loop Until There Is Nothing Left Of @String
WHILE @IntPos > 0
BEGIN
-- Extract The String
SET @SubStr = SUBSTRING(@String, 0, @IntPos)
-- Insert The String Into The Table
INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
-- Remove The String & Comma Separator From The Original
SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
-- Get The New Index To The String
SET @IntPos = CHARINDEX(',', @String)
END
-- Return The Last One
INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END
|
|
Other 5 submission(s) by this author
|
|
Report Bad Submission
|
Your Vote
|
| |
Other User Comments
|
 11/12/2006 2:29:52 PM: Jeff Moden
You appear to have a bit of an oversite in your function... try this to see what I mean...
SELECT * FROM dbo.ParseByComma('1,2,3,3,3,3,4,5,6,7,8')
(If this comment was disrespectful, please report it.)
|
11/13/2006 9:21:11 AM: Jeff Moden
I wasn't going to vote until the author of the code fixed a bug I highlited in my previous message. But, since someone else voted a high vote on this code that produces unexpected results in the face of duplication, I had to vote pretty low because the code does not work as advertised. Sorry...
(If this comment was disrespectful, please report it.)
|
11/13/2006 11:22:34 AM: Dave Vroman
This is not an error or bug. The "fix" for you is to use "SELECT DISTINCT" which is very standard in SQL. Putting the "DISTINCT" code in the routine would introduce possible unintended errors.
(If this comment was disrespectful, please report it.)
|
11/13/2006 7:21:20 PM: Jeff Moden
And what if the number 3 is actually required more than once for the different parameters... DISTINCT is going to do anything except make sure that I don't get what I want. The function does not return all of the parameters I pass it. That's a bug.
(If this comment was disrespectful, please report it.)
|
11/14/2006 3:01:34 PM: Dave Vroman
try it now
(If this comment was disrespectful, please report it.)
|
11/14/2006 11:30:59 PM: Chris
Jeff is correct. This function is flawed and although it might suite your implmentation it could hurt alot of other unweiry people.
(If this comment was disrespectful, please report it.)
|
11/16/2006 8:47:13 PM: Jeff Moden
Much better! I tried real hard to break it and couldn't. I even tried the following "ACE" breakers...
SELECT *
FROM dbo.ParseByComma(',1,,,,2,')
SELECT *
FROM dbo.ParseByComma('')
SELECT *
FROM dbo.ParseByComma(NULL)
(If this comment was disrespectful, please report it.)
|
11/16/2006 8:50:23 PM: Jeff Moden
p.s. It's kinda cool that this forum let's me change my vote... I changed it from a 1 to a 5 not only because of the correction Dave made, but because he also included embedded comments for clarification. Nicely done, Dave.
(If this comment was disrespectful, please report it.)
|
11/16/2006 8:51:43 PM: Jeff Moden
AND... because Dave actually took the time to make a correction to his code, I encourage you to vote on this code, as well.
(If this comment was disrespectful, please report it.)
|
9/12/2007 12:08:18 PM: David
I don't understand why this works:
SELECT users.lname, users.practice
FROM users WHERE practice IN (SELECT * FROM dbo.ufn_ParseByComma( '110,1,2' ));
And this doesn't:
SELECT users.lname, users.practice
FROM users WHERE practice IN (SELECT * FROM dbo.ufn_ParseByComma( users.practice ));
users.practice is a string. What am I missing?
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
To post feedback, first please login.
|
