Results 1 to 7 of 7

Thread: SQL Server 2005 - Split string into table using WITH recursion (no loops)

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    SQL Server 2005 - Split string into table using WITH recursion (no loops)

    This code is faster than using loops
    sql Code:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20091104>
    4. -- Description: <Split string into table, separator can be more than 1 char>
    5. -- =============================================
    6. CREATE FUNCTION [dbo].[fn_SplitStringToTable]
    7. (
    8.       @DataList NVARCHAR(MAX)
    9.     , @Separator NVARCHAR(MAX)
    10. )
    11. RETURNS @tbl TABLE (
    12.       RowIndex INT PRIMARY KEY
    13.     , FromPos INT
    14.     , ToPos INT
    15.     , ItemData NVARCHAR(MAX)
    16. )
    17. AS
    18. BEGIN
    19.     -- SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231,111', ',')
    20.    
    21.     DECLARE @LenSep INT
    22.     SET @LenSep = DATALENGTH(@Separator) / 2
    23.    
    24.     IF @LenSep > 0 AND DATALENGTH(@DataList) > 0 BEGIN
    25.         ; WITH res (RowIndex, FromPos, ToPos) AS (
    26.             SELECT CAST(1 AS INT) AS RowIndex
    27.                 , CAST(1 AS INT) AS FromPos
    28.                 , CAST(CHARINDEX(@Separator, @DataList + @Separator) AS INT) AS ToPos
    29.            
    30.             UNION ALL
    31.            
    32.             SELECT CAST(RowIndex + 1 AS INT) AS RowIndex
    33.                 , CAST(res.ToPos + @LenSep AS INT) AS FromPos
    34.                 , CAST(CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) AS INT) AS ToPos
    35.             FROM res
    36.             WHERE CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) > 0
    37.         )
    38.         INSERT INTO @tbl
    39.         SELECT res.*, SUBSTRING(@DataList, FromPos, ToPos - FromPos) AS ItemData
    40.         FROM res
    41.         OPTION (MAXRECURSION 0)
    42.     END
    43.    
    44.     RETURN
    45. END
    Last edited by CVMichael; Dec 15th, 2011 at 11:18 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)

    recursive CTEs are so awesome.... I'll be adding this to my list of tricks.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    New Member
    Join Date
    Feb 2008
    Posts
    9

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)


  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)

    It appears that you have posted an invalid link, perhaps the discussion (about what?) was deleted.

  5. #5
    New Member
    Join Date
    Feb 2008
    Posts
    9

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)

    hmm ... the link works for me.
    Lets try again:
    http://www.sqlservercentral.com/Foru...39-1683-1.aspx

    "http://www.sqlservercentral.com/Forums/Topic817039-1683-1.aspx"

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)

    It still doesn't work for me I'm afraid - it just redirects to their main page (I can open other threads there using similar URLs).

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)

    si_the_geek, that's because you have to register to that forum, and be logged-in when you click on the link.

    It does not work for me either when I'm not logged-in

Tags for this Thread

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