SQL Server 2005 - Loop through/split a delimited string

[timeshifter]

I'm sure I'm not the only one who's had times where I need to insert several items into a table, but only one field is different between them. The scenario that led me to write this particular loop was a security system. One table contains a list of roles, another table contains user information, and a third table contains lists of what users have what role levels. A global admin needed open access to everything, so they'd need one entry in the UserRoles table per role definition. For me, this came to about 12 entries. And I don't care if it's an intranet application, I am NOT writing an app that will make 12 database hits to accomplish one task. The solution? Send SQL a comma-separated list and loop through it, breaking off only what I need to insert on that particular iteration.

Naturally, I used Google looking for code, but it all seemed too complicated. Most of them tried to preserve the original list, which is all but useless once the function is done. They also held on to two index variables, one for the start of the string and one for the end, based on where the delimiter is. I thought, "well, that's kinda dumb...", so I set to work on this, which is much smaller, cleaner, and accomplishes the same thing.

Basically, it has three variables. @strRoles is a comma-separated list that is defined as a parameter. @pos is an int used to mark the location of the next delimiter, and @RoleID is used to store the extracted piece of information.

This function absolutely relies on a delimiter after every data piece, so the first check is to make sure it's there. Pretty simple:

IF substring(@strRoles, LEN(@strRoles)-1,1)<>','
        SET @strRoles = @strRoles + ',' --add a comma to the end if it isn't there

Next, I set the @pos variable and declare my result. In this example, the data pieces are all ints, but if you need to pull out strings, you'd use varchar() instead.

SET @pos=0
    DECLARE @RoleID as int

Now comes the loop itself. The loop is checking to make sure the delimiter is still there, namely a comma. The first thing it does is set our temp variable, @RoleID, equal to the substring of the data string to the first index of the delimiter. No mess there. Then it inserts into the table I need it to, where @UserID is also a defined parameter. The last thing it does is reassign the data string to NOT include the item it just inserted, basically chopping off the first item, and the following delimiter.

WHILE charindex(',',@strRoles)>0
    BEGIN
        SET @RoleID = cast(substring(@strRoles,0, charindex(',',@strRoles)) as int)
        INSERT INTO [tblUserRoles] ([UserID], [RoleID]) VALUES (@UserID, @RoleID)
        SET @strRoles = substring(@strRoles, charindex(',',@strRoles)+1, LEN(@strRoles) - @pos) --remove the first item from the list
    END

If I passed it "1,4,12,8" as the @strRoles parameter, this is what it looks like after each pass through the loop:

parameter     >> "1,4,12,8"
add delimiter >> "1,4,12,8,"
first pass    >> "4,12,8,"
second pass   >> "12,8,"
third pass    >> "8,"
fourth pass   >> "" , WHILE is no longer met, loop ends

I hope this saves somebody the trouble that I've gone through to find a reliable split operation in T-SQL!

[CVMichael]

Here's my Split function for T-SQL:

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20080124>
-- Description: <>
-- =============================================
CREATE FUNCTION dbo.fnSplit
(
      @Expression NVARCHAR(max)
    , @Delimiter  NVARCHAR(max)
    , @Index      INT
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @Return  NVARCHAR(max)
    DECLARE @Pos     INT
    DECLARE @PrevPos INT
    DECLARE @I       INT
    
    -- SELECT dbo.fnSplit('4.55.108.2','.', 2)
    
    IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @Index < 1
        SET @Return = NULL
    ELSE IF @Index = 1 BEGIN
        SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
        IF @Pos > 0 SET @Return = LEFT(@Expression, @Pos - 1)
    END ELSE BEGIN
        SET @Pos = 0
        SET @I = 0
        
        WHILE (@Pos > 0 AND @I < @Index) OR @I = 0 BEGIN
            SET @PrevPos = @Pos
            SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
            
            SET @I = @I + 1
        END
        
        IF @Pos = 0 AND @I = @Index
            SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
        ELSE IF @Pos = 0 AND @I <> @Index
            SET @Return = NULL
        ELSE
            SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
    END
    
    RETURN @Return
END

Example use:
SELECT dbo.fnSplit('4.55.108.2','.', 2)
Will return: "55"

[timeshifter]

Pretty slick, I must admit, albeit a slightly different purpose from what the code I posted is. I can see yours being great for say a card number that's delimited by dashes, but a certain level of identification only occurs within one part of it. The script I posted is more for the idea that every part needs to be used. Cool function though.

[CVMichael]

Well, I got bored and I made a function that generates rows depending on the parameter:

-- =============================================
-- Author:		<Michael Ciurescu>
-- Create date: <20090202>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[f_GenerateRows]
	(@NumRows INT)
RETURNS 
	@Tbl TABLE (pkID INT PRIMARY KEY NOT NULL)
AS
BEGIN
	DECLARE @i INT;
	
	-- SELECT * FROM dbo.f_GenerateRows((SELECT 10))
	
	SET @I = 1
	WHILE @I <= @NumRows BEGIN
		INSERT INTO @Tbl VALUES(@I)
		
		SET @I = @I + 1
	END
	
	RETURN
END

Then using that function + the fnSplit function in the previous post, I made it split a string and return in a table format:

DECLARE @IP NVARCHAR(20)
SET @IP = '4.55.108.2'

DECLARE @Delimiter NVARCHAR(10)
SET @Delimiter = '.'

SELECT pkID AS [Index], dbo.fnSplit(@IP, @Delimiter, pkID) AS IP_Part
FROM dbo.f_GenerateRows((SELECT 1 + (LEN(@IP) - LEN(REPLACE(@IP, @Delimiter, ''))) / LEN(@Delimiter)))

Now, if you DON'T want to use the fnSplit function the previous post, then you can do this:

DECLARE @IP NVARCHAR(20)
SET @IP = '.4.55.108.2.'

DECLARE @Delimiter NVARCHAR(10)
SET @Delimiter = '.'

SELECT ROW_NUMBER() OVER (ORDER BY x.pkID) AS ID, SUBSTRING(x.IP, x.pkID + LEN(@Delimiter), y.pkID - x.pkID - LEN(@Delimiter)) AS IP_Part
FROM (
	SELECT pkID, @IP AS IP
	FROM dbo.f_GenerateRows((SELECT LEN(@IP)))
	WHERE SUBSTRING(@IP, pkID, LEN(@Delimiter)) = @Delimiter 
) AS x
CROSS JOIN (
	SELECT pkID, @IP AS IP
	FROM dbo.f_GenerateRows((SELECT LEN(@IP)))
	WHERE SUBSTRING(@IP, pkID, LEN(@Delimiter)) = @Delimiter
) AS y
WHERE x.pkID < y.pkID AND CHARINDEX(@Delimiter, SUBSTRING(x.IP, x.pkID + LEN(@Delimiter), y.pkID - x.pkID - LEN(@Delimiter))) = 0

NOTE: In previous code, you have to put the delimiter at the beginning and end of string.

This is the result (for both scripts)

ID	IP_Part
1	4
2	55
3	108
4	2

[Anhlunnhaque]

@CVMichael,

Is possible to split yup the string invidually in separate columns?

Thanks!!!

Here's my Split function for T-SQL:

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20080124>
-- Description: <>
-- =============================================
CREATE FUNCTION dbo.fnSplit
(
      @Expression NVARCHAR(max)
    , @Delimiter  NVARCHAR(max)
    , @Index      INT
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @Return  NVARCHAR(max)
    DECLARE @Pos     INT
    DECLARE @PrevPos INT
    DECLARE @I       INT
    
    -- SELECT dbo.fnSplit('4.55.108.2','.', 2)
    
    IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @Index < 1
        SET @Return = NULL
    ELSE IF @Index = 1 BEGIN
        SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
        IF @Pos > 0 SET @Return = LEFT(@Expression, @Pos - 1)
    END ELSE BEGIN
        SET @Pos = 0
        SET @I = 0
        
        WHILE (@Pos > 0 AND @I < @Index) OR @I = 0 BEGIN
            SET @PrevPos = @Pos
            SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
            
            SET @I = @I + 1
        END
        
        IF @Pos = 0 AND @I = @Index
            SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
        ELSE IF @Pos = 0 AND @I <> @Index
            SET @Return = NULL
        ELSE
            SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
    END
    
    RETURN @Return
END

Example use:
SELECT dbo.fnSplit('4.55.108.2','.', 2)
Will return: "55"

[CVMichael]

Well, you can call it multiple times, for each column, for example:

SELECT dbo.fnSplit(my_data, '.', 1) AS Col_A, dbo.fnSplit(my_data, '.', 2) AS Col_B, dbo.fnSplit(my_data, '.', 3) AS Col_C, dbo.fnSplit(my_data, '.', 4) AS Col_D

[Anhlunnhaque]

that works..why didn't I think of that..hehe...Thank you for your help...

thanks again..!!!