Technical Article

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

,

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
43
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

Rate

2.43 (14)

You rated this post out of 5. Change rating

Share

Share

Rate

2.43 (14)

You rated this post out of 5. Change rating