How can I separated comma be data set in condition?

Question

I'm very new to the whole SQL thing. I have a list of account id's which I converted to a comma separated string from Splunk and would like to build a SQL query where I can execute and receive all those in that array. I tried this but it only returns the first one

CODE

DECLARE @accountIdlist varchar(8000) = '1e4976b4-c501-42d2-a28c-a89300b24229, 40b74b7c-a32a-4faf-8693-a8e500fc60fc';
SELECT [FirstName]     
      ,[Surname]
      ,[Number]     
      ,[Email]
  FROM customers where accountid IN (@accountIdlist) order by ID desc

D-Shih · Answer 1 · 2018-06-23T19:40:30.363

You can try to use STRING_SPLIT function split your data, then write a subquery in where clause.

STRING_SPLIT ( string , separator )

SELECT [FirstName]     
      ,[Surname]
      ,[Number]     
      ,[Email]
FROM customers 
where accountid IN (select value from STRING_SPLIT(@accountIdlist,',')) 
order by ID desc

I will recommend you use exists

SELECT [FirstName]     
      ,[Surname]
      ,[Number]     
      ,[Email]
FROM customers 
where exists (select 1 
              from STRING_SPLIT(@accountIdlist,',')
              where accountid = value
             )

sqlfiddle:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bdeebda14bc88f303a7c5c5752c36039

EDIT

If your sql-server version didn't support STRING_SPLIT you can write SPLIT by yourself.

Here is a sample for you.

CREATE FUNCTION dbo.Spite_String  (@input varchar(max),@Split char(1))
RETURNS @rtnTable TABLE 
(
    Value varchar(1000)
)
AS
BEGIN
   DECLARE @XmlData xml;

   SELECT @XmlData = CONVERT(xml,'<root><s>' + REPLACE(@input,@Split,'</s><s>') + '</s></root>');

  insert into @rtnTable (Value)
  SELECT [Value] = T.c.value('.','varchar(1000)')
  FROM @XmlData.nodes('/root/s') T(c)
  return
END

then you can use like

SELECT [FirstName]     
      ,[Surname]
      ,[Number]     
      ,[Email]
FROM customers 
where exists (select 1 
              from Spite_String(@accountIdlist,',')
              where accountid = value
             )

sqlfiddle:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=debc6857a970c892f175307e3e840877

although correct, and I have marked up, the OP might not have a split string function so might be worth editing the answer to show how to do this — Simon Price, Jun 23 '18 at 19:08

score 0 · Accepted Answer · answered Jun 23 '18 at 19:11

0

Use a SQL function to convert your varchar list to a temp table and join on the result set (see this accepted answer).

answered Jun 23 '18 at 19:11

Mitch Stewart

1,253
10
12

score 0 · Answer 3 · answered Jun 23 '18 at 20:32

The simplest method is probably to use like:

SELECT [FirstName], [Surname], [Number], [Email]
FROM customers 
WHERE ', ' + @accountIdList + ', ' LIKE '%, accountid, %' 
ORDER BY ID DESC;

This is definitely not the most efficient method, but it is possibly the simplest to implement.

How can I separated comma be data set in condition?

3 Answers3