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

(14)

You rated this post out of 5. Change rating

Share

Share

Rate

(14)

You rated this post out of 5. Change rating