Paul S Paul S - 6 months ago 14
SQL Question

Break down a delimited string into a temporary table

I am sending a delimited string to a 'Stored Procedure' which eventually will be used with an IN statement to update some records, so what im trying to do is create a 'Table-Valued Function' which will do this conversion and return this table that will be usable within this update statement but im unsure how to do this.

There is another function that breaks down delimited strings but it doesnt like like it returns an entire table, i dont really understand its sytax, its not any sql i have come accross before......

So (ill post the other function below) can i modify or use this function for an arbitrary amount of values in this delimited string or do i need to come up with another method?

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)


when using this function ill get each item with syntax like the following

set @ID = (select Data from dbo.Split(@Params, '|') where ID = 1)


*****UPDATE WITH RESULTS FROM THE GIVEN ANSWER*******

DECLARE @pVals VARCHAR(MAX)

SET @pvals = '1,2,3,4,5'

DECLARE @tblDelimitedData TABLE (
[Data] INT NULL
)

INSERT INTO @tblDelimitedData(data)
SELECT data
FROM dbo.Split(@pvals, ',') s
WHERE ID >= 2

SELECT * FROM @tblDelimitedData


Results are 2, 3, 4, 5

Perfect Thanks!!

Answer

To insert the result of the function to a temporary table, you should use:

INSERT INTO #tempTable(data)
SELECT data
FROM dbo.Split(@Params, '|') s
WHERE ID = 1

Using WHERE ID = 1, however, only inserts the first item in the delimited list. If you want to filter then number of values to be inserted in the table use <=.

For example, you only want to insert the first three values, then you use:

WHERE ID <= 3

Additionally, your function to split is slower compared to some other available functions. I recommend that you a Tally-based splitter such as Jeff Moden's DelimitedSplit8K.