Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Split string into table, separator can be more than 1 char

By Luigi Marinescu, 2009/11/19

This function has been developed by me and my friend Mihai Ciurescu.

Let's suppose that you have a web page with a listbox (multiple selection) of States and you want to get all customers having properties within your selected States. So, your page will send to the SQL Server a list of all selected State_IDs.

Now, your list of IDs is like '123,43,5465,6788,1231' and you want to split and pivot them into a table like:

Your_Value

123

5465

6788

1231

Using this function now you can use its table-result to do an INNER JOIN with your_Address table (where you store the State_Id) ON .fn_SplitStringToTable.ItemData = your_Address.State_Id

This is what this

SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231', ',')

returns

RowIndex FromPos ToPos ItemData
1 1 4 123
2 5 7 43
3 8 12 5465
4 13 17 6788
5 18 22 1231

Enjoy!

Luigi and Michael

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Luigi Marinescu and 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 @ret TABLE 
(
	RowIndex INT
	, FromPos INT
	, ToPos INT
	, ItemData NVARCHAR(MAX)
)
AS
BEGIN
	DECLARE @LenSep INT
	SET @LenSep = LEN(@Separator)
-- SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231,111', ',')
	; 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 @ret
	SELECT res.*, SUBSTRING(@DataList, FromPos, ToPos - FromPos) AS ItemData
	FROM res
	
	RETURN 
END