Results 1 to 7 of 7

Thread: SQL Server 2005 - Loop through/split a delimited string

  1. #1

    Thread Starter
    Banned timeshifter's Avatar
    Join Date
    Mar 2004
    Location
    at my desk
    Posts
    2,465

    SQL Server 2005 - Loop through/split a delimited string

    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:
    t-sql Code:
    1. IF substring(@strRoles, LEN(@strRoles)-1,1)<>','
    2.         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.
    t-sql Code:
    1. SET @pos=0
    2.     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.
    t-sql Code:
    1. WHILE charindex(',',@strRoles)>0
    2.     BEGIN
    3.         SET @RoleID = cast(substring(@strRoles,0, charindex(',',@strRoles)) as int)
    4.         INSERT INTO [tblUserRoles] ([UserID], [RoleID]) VALUES (@UserID, @RoleID)
    5.         SET @strRoles = substring(@strRoles, charindex(',',@strRoles)+1, LEN(@strRoles) - @pos) --remove the first item from the list
    6.     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:
    Code:
    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!

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,803

    Re: SQL Server 2005 - Loop through/split a delimited string

    Here's my Split function for T-SQL:
    sql Code:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20080124>
    4. -- Description: <>
    5. -- =============================================
    6. CREATE FUNCTION dbo.fnSplit
    7. (
    8.       @Expression NVARCHAR(max)
    9.     , @Delimiter  NVARCHAR(max)
    10.     , @Index      INT
    11. )
    12. RETURNS NVARCHAR(max)
    13. AS
    14. BEGIN
    15.     DECLARE @Return  NVARCHAR(max)
    16.     DECLARE @Pos     INT
    17.     DECLARE @PrevPos INT
    18.     DECLARE @I       INT
    19.    
    20.     -- SELECT dbo.fnSplit('4.55.108.2','.', 2)
    21.    
    22.     IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @Index < 1
    23.         SET @Return = NULL
    24.     ELSE IF @Index = 1 BEGIN
    25.         SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
    26.         IF @Pos > 0 SET @Return = LEFT(@Expression, @Pos - 1)
    27.     END ELSE BEGIN
    28.         SET @Pos = 0
    29.         SET @I = 0
    30.        
    31.         WHILE (@Pos > 0 AND @I < @Index) OR @I = 0 BEGIN
    32.             SET @PrevPos = @Pos
    33.             SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
    34.            
    35.             SET @I = @I + 1
    36.         END
    37.        
    38.         IF @Pos = 0 AND @I = @Index
    39.             SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
    40.         ELSE IF @Pos = 0 AND @I <> @Index
    41.             SET @Return = NULL
    42.         ELSE
    43.             SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
    44.     END
    45.    
    46.     RETURN @Return
    47. END
    Example use:
    SELECT dbo.fnSplit('4.55.108.2','.', 2)
    Will return: "55"

  3. #3

    Thread Starter
    Banned timeshifter's Avatar
    Join Date
    Mar 2004
    Location
    at my desk
    Posts
    2,465

    Re: SQL Server 2005 - Loop through/split a delimited string

    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.

  4. #4
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,803

    Re: SQL Server 2005 - Loop through/split a delimited string

    Well, I got bored and I made a function that generates rows depending on the parameter:
    Code:
    -- =============================================
    -- 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:
    Code:
    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:
    Code:
    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)
    Code:
    ID	IP_Part
    1	4
    2	55
    3	108
    4	2
    Last edited by CVMichael; Feb 2nd, 2009 at 04:50 PM.

  5. #5
    New Member
    Join Date
    Mar 2009
    Posts
    2

    Re: SQL Server 2005 - Loop through/split a delimited string

    @CVMichael,

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

    Thanks!!!



    Quote Originally Posted by CVMichael
    Here's my Split function for T-SQL:
    sql Code:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20080124>
    4. -- Description: <>
    5. -- =============================================
    6. CREATE FUNCTION dbo.fnSplit
    7. (
    8.       @Expression NVARCHAR(max)
    9.     , @Delimiter  NVARCHAR(max)
    10.     , @Index      INT
    11. )
    12. RETURNS NVARCHAR(max)
    13. AS
    14. BEGIN
    15.     DECLARE @Return  NVARCHAR(max)
    16.     DECLARE @Pos     INT
    17.     DECLARE @PrevPos INT
    18.     DECLARE @I       INT
    19.    
    20.     -- SELECT dbo.fnSplit('4.55.108.2','.', 2)
    21.    
    22.     IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @Index < 1
    23.         SET @Return = NULL
    24.     ELSE IF @Index = 1 BEGIN
    25.         SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
    26.         IF @Pos > 0 SET @Return = LEFT(@Expression, @Pos - 1)
    27.     END ELSE BEGIN
    28.         SET @Pos = 0
    29.         SET @I = 0
    30.        
    31.         WHILE (@Pos > 0 AND @I < @Index) OR @I = 0 BEGIN
    32.             SET @PrevPos = @Pos
    33.             SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
    34.            
    35.             SET @I = @I + 1
    36.         END
    37.        
    38.         IF @Pos = 0 AND @I = @Index
    39.             SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
    40.         ELSE IF @Pos = 0 AND @I <> @Index
    41.             SET @Return = NULL
    42.         ELSE
    43.             SET @Return = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
    44.     END
    45.    
    46.     RETURN @Return
    47. END
    Example use:
    SELECT dbo.fnSplit('4.55.108.2','.', 2)
    Will return: "55"

  6. #6
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,803

    Re: SQL Server 2005 - Loop through/split a delimited string

    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

  7. #7
    New Member
    Join Date
    Mar 2009
    Posts
    2

    Re: SQL Server 2005 - Loop through/split a delimited string

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

    thanks again..!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width