How to manipulate comma-separated list in SQL Server

Question

I have a list of values such as

1,2,3,4...

that will be passed into my SQL query.

I need to have these values stored in a table variable. So essentially I need something like this:

declare @t (num int)
insert into @t values (1),(2),(3),(4)...

Is it possible to do that formatting in SQL Server? (turning 1,2,3,4... into (1),(2),(3),(4)...

Note: I can not change what those values look like before they get to my SQL script; I'm stuck with that list. also it may not always be 4 values; it could 1 or more.

Edit to show what values look like: under normal circumstances, this is how it would work:

select t.pk 
from a_table t
where t.pk in (#place_holder#)

#placeholder# is just a literal place holder. when some one would run the report, #placeholder# is replaced with the literal values from the filter of that report:

select t.pk 
from a_table t
where t.pk in (1,2,3,4) -- or whatever the user selects

t.pk is an int

note: doing

declare @t as table (
num int
)

insert into @t values (#Placeholder#)

does not work.

The following answer shows a way to do this with a table-valued function: http://stackoverflow.com/a/5401777/13087 — Joe, May 19 '16 at 18:37
This shows you how to use Table Valued Parameters http://stackoverflow.com/questions/30108973/pass-list-of-integers-to-stored-procedure — Dan Bracuk, May 19 '16 at 18:43
whats being passed to my sql script literally looks like 1,2,3,4 . its not a string, if I could convert that to a string, then yes, these answers would work. — Just_Some_Guy, May 19 '16 at 18:45
What data type is that 'thing' being passed into your script? In other words, how do you declare it in your script? — devlin carnate, May 19 '16 at 18:57
In this answer I added up some nice [tricks you can do with XML and string values](http://stackoverflow.com/a/33658220/5089204). Go to the "Dynamic IN" section. Hope this helps. — Shnugo, May 19 '16 at 19:10
@Just_Some_Guy - we really need to see how 1,2,3,4 is accepted into your script. It must have some kind of data type definition. — devlin carnate, May 19 '16 at 19:20
@devlincarnate Its coming from a filter on a report from a webserver. I have no access to change how those parameters are passed through to my script. when it gets to my script those values would be interpreted as Ints in a normal circumstance. This is one of the few times that method will not work. — Just_Some_Guy, May 19 '16 at 19:22
@Just_Some_Guy - is this script a stored procedure? a parameterized query? or what? — devlin carnate, May 19 '16 at 19:24
And without a data type, you are going to be out of luck. SQL requires a known data type in order to operate on a value. — devlin carnate, May 19 '16 at 19:26
@Just_Some_Guy - Are you able to do something like this? `DECLARE @test VARCHAR(10) = '#placeholder#'` ? — devlin carnate, May 19 '16 at 19:31
I see what your trying to say; but if I type out '#placeholder#' in the script, I'll end up with '1','2','3','4' and not '1,2,3,4'. — Just_Some_Guy, May 19 '16 at 19:52
@Just_Some_Guy - then can't you just do : `insert into @t values (#placeholder#)` ? — devlin carnate, May 19 '16 at 20:02
Hi @Just_Some_Guy, Thx for marking my answer as accepted! Do you have a reason, not to vote my answer up? Might be you are not aware of the fact, that accepting and voting are two separate steps. Acceptance is great to mark a question as closed and votes are greate as they are a measurement tool for quality and bound to *tag badges* and privileges... Happy Coding! — Shnugo, May 20 '16 at 14:58

score 0 · Accepted Answer · edited May 23 '17 at 12:23

Your description is a bit ridicuolus, but you might give this a try:

Whatever you mean with this

I see what your trying to say; but if I type out '#placeholder#' in the script, I'll end up with '1','2','3','4' and not '1,2,3,4'

I assume this is a string with numbers, each number between single qoutes, separated with a comma:

DECLARE @passedIn VARCHAR(100)='''1'',''2'',''3'',''4'',''5'',''6'',''7''';
SELECT @passedIn; -->: '1','2','3','4','5','6','7'

Now the variable @passedIn holds exactly what you are talking about I'll use a dynamic SQL-Statement to insert this in a temp-table (declared table variable would not work here...)

CREATE TABLE #tmpTable(ID INT);

DECLARE @cmd VARCHAR(MAX)=
'INSERT INTO #tmpTable(ID) VALUES (' + REPLACE(SUBSTRING(@passedIn,2,LEN(@passedIn)-2),''',''','),(') + ');';

EXEC (@cmd);

SELECT * FROM #tmpTable;
GO

DROP TABLE #tmpTable;

UPDATE 1: no dynamic SQL necessary, all ad-hoc...

You can get the list of numbers as derived table in a CTE easily. This can be used in a following statement like WHERE SomeID IN(SELECT ID FROM MyIDs) (similar to this: dynamic IN section )

    WITH MyIDs(ID) AS
    (
        SELECT A.B.value('.','int') AS ID
        FROM
        ( 
        SELECT CAST('<x>' + REPLACE(SUBSTRING(@passedIn,2,LEN(@passedIn)-2),''',''','</x><x>') + '</x>' AS XML) AS AsXml
        ) as tbl
        CROSS APPLY tbl.AsXml.nodes('/x') AS A(B)
    )
    SELECT * FROM MyIDs

UPDATE 2:

And to answer your question exactly:

With this following the CTE

insert into @t(num)
SELECT ID FROM MyIDs

... you would actually get your declared table variable filled - if you need it later...

How to manipulate comma-separated list in SQL Server

1 Answers1

UPDATE 1: no dynamic SQL necessary, all ad-hoc...

UPDATE 2:

Linked