How To Loop Through A Comma Delimited String In SQL

Looping through a comma delimited string in SQL is easy to do, but can take a few minutes to write and test the scripts, which I never really have the time for, so now I have a permanent home for this script for me to reference.

Here’s what I setup this script for (you can easily modify it):

I have a registration form that accepts Degree Types(e.g., MD, PHD, MS, etc.) to be  freely typed in a textbox (I know, I hate this too) so that doctors don’t have to select them from a listbox, which I think is much easier, but the issue is obvious – you can’t just insert a string of comma-delimited degrees into the database, that is if your database is properly structured (many to many relationship) anyway, so I needed to accept the string into my Stored Procedure and then break the comma-delimited string up so that I can insert it into the database. Anyhow, feel free to copy and adjust this script as needed.

//----------------------------------------------------------
DECLARE @arrItems         VARCHAR(1000)  SET @arrItems = 'MD,PHD,MS'
DECLARE @Item             VARCHAR(50)
DECLARE @Position         INT
DECLARE @DegreeID INT
DECLARE @Loop             BIT
--Make sure we enter the loop, even if there's only one item
IF(right(@arrItems,1) <> ',' and Len(@arrItems)>0)
BEGIN
Set
@arrItems = @arrItems + ','
END
 

SET @Loop = CASE WHEN LEN(@arrItems) > 0 THEN 1 ELSE 0 END
WHILE (SELECT @Loop) = 1
BEGIN
SELECT @Position = CHARINDEX(',', @arrItems, 1)

IF(@Position > 0)
BEGIN
SELECT @Item         = SUBSTRING(@arrItems, 1, @Position - 1)
SELECT @arrItems     = SUBSTRING(@arrItems, @Position + 1, LEN(@arrItems) -                                                         @Position)

-- Look up Degree ID
SET @DegreeID = (Select Degree.fld_Degree_ID from tbl_Lookup_Degrees Degree
where Degree.fld_Degree_Name = @Item)

-- Make sure the database found and returned a Degree ID
IF(@DegreeID <> 0)
BEGIN
--Insert person's degree into the database
Insert into tbl_Person_By_Degree(fld_Person_ID, fld_Degree_ID)
Values(@PersonID, @DegreeID)

END
END
ELSE
BEGIN
SELECT @Item = @arrItems
SELECT @Loop = 0
END
END


11 Comments

  1. Just what I needed, thanks.

  2. Thanks, this helped me solve a comma delimited problem. I had to loop through a list of contracts and check each one (which was sent to me as a comma delimited string) and do some processing on each of those contracts.

  3. teresa

    Thanks for sharing. Helped a lot.

  4. First of all I would like to say awesome blog!
    I had a quick question in which I’d like to ask if you do not mind. I was curious to know how you center yourself and clear your head prior to writing. I’ve had a difficult time clearing my thoughts in getting my
    ideas out there. I do enjoy writing however it just seems
    like the first 10 to 15 minutes are lost just trying to figure out how to begin.
    Any recommendations or tips? Thanks!

  5. This is good, it was helpful to me. Thanks

  6. But good article. Hard to Read the code block

Leave a Reply