Break down a delimited string into a temporary table

Question

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

There is a similar question solved at the link [here](http://stackoverflow.com/questions/36793821/to-convert-a-string-into-table-with-schema). Hope this helps. — Shikhar Maheshwari, Jun 01 '16 at 07:24
Why not use a data type *designed* for storing multiple values, such as a table-valued parameter or XML, rather than stuffing everything into a string and then having to undo that? — Damien_The_Unbeliever, Jun 01 '16 at 07:37
@Damian_The_Unbeliever, This is going to be called from something im developing which is currently passing delimited strings to stored procedure on the server, its nice and simple and to be honest SQL is quite new to me so im edging towards sticking with whats working so far so i dont make a mess of things on the server side :) — Paul S, Jun 01 '16 at 07:41
@Shikar Thanks, i had a look and it looks interesting but its for 2 different delimiters and unfortunately i dont quite know enough to modify it or understand it properly yet :) — Paul S, Jun 01 '16 at 07:43
back @Damian, i am actually trying to get it into a tablevalued function which is i think what you stated, but its coming in from outside as a delimited string and im not quite sure how i would send it directly to an SP as a tablevalued variable — Paul S, Jun 01 '16 at 07:45

Felix Pamittan · Accepted Answer · 2016-06-01T07:28:31.393

3

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.

edited Jun 01 '16 at 07:28

answered Jun 01 '16 at 07:23

Felix Pamittan

31,544
7
41
67

so to insert all the values after the first value i can use => 2 ..... the first value will be used as the value to update too. – Paul S Jun 01 '16 at 07:27
@PaulS, yes to insert starting from the 2nd value, use `ID >= 2`. – Felix Pamittan Jun 01 '16 at 07:27
ah yes sorry >= 2... not => :), im a VB programmer can you tell haha. thanks for the info about the DelimitedSplit8K. i didnt write the original one and im taking over these mundane sql tasks so the sql expert can do more pressing work :/ but its alot of fun :) – Paul S Jun 01 '16 at 07:36

Break down a delimited string into a temporary table

1 Answers1

Linked