-
Nov 5th, 2009, 11:29 AM
#1
SQL Server 2005 - Split string into table using WITH recursion (no loops)
This code is faster than using loops
sql Code:
-- =============================================
-- Author: <Michael Ciurescu>
-- Create date: <20091104>
-- Description: <Split string into table, separator can be more than 1 char>
-- =============================================
CREATE FUNCTION [dbo].[fn_SplitStringToTable]
(
@DataList NVARCHAR(MAX)
, @Separator NVARCHAR(MAX)
)
RETURNS @tbl TABLE (
RowIndex INT PRIMARY KEY
, FromPos INT
, ToPos INT
, ItemData NVARCHAR(MAX)
)
AS
BEGIN
-- SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231,111', ',')
DECLARE @LenSep INT
SET @LenSep = DATALENGTH(@Separator) / 2
IF @LenSep > 0 AND DATALENGTH(@DataList) > 0 BEGIN
; WITH res (RowIndex, FromPos, ToPos) AS (
SELECT CAST(1 AS INT) AS RowIndex
, CAST(1 AS INT) AS FromPos
, CAST(CHARINDEX(@Separator, @DataList + @Separator) AS INT) AS ToPos
UNION ALL
SELECT CAST(RowIndex + 1 AS INT) AS RowIndex
, CAST(res.ToPos + @LenSep AS INT) AS FromPos
, CAST(CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) AS INT) AS ToPos
FROM res
WHERE CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) > 0
)
INSERT INTO @tbl
SELECT res.*, SUBSTRING(@DataList, FromPos, ToPos - FromPos) AS ItemData
FROM res
OPTION (MAXRECURSION 0)
END
RETURN
END
Last edited by CVMichael; Dec 15th, 2011 at 11:18 AM.
-
Nov 5th, 2009, 11:39 AM
#2
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
-
Nov 23rd, 2009, 10:51 AM
#3
New Member
Re: SQL Server 2005 - Split string into table using WITH recursion (no loops)
-
Nov 24th, 2009, 05:00 AM
#4
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.
-
Nov 24th, 2009, 10:29 AM
#5
New Member
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"
-
Nov 24th, 2009, 11:51 AM
#6
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).
-
Nov 24th, 2009, 11:53 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|