Comma separated values In An IN statement (SQL)

Question

I have a table with a column of comma separated values, i want to take them as different values and place inside The In clause of another select statement. i've tried with cross apply but didn't get it working properly

the table (T1) looks like :

Empcode Eid Unitcodes
007645  164 UNT111$UNT112$UNT113$
000645  162 UNT100$UNT102$UNT20$UNT97$UNT98$UNT99$UNT136$
002585  163 UNT25$UNT39$
003059  180 UNT76$                                                                                              
000559  165 UNT109$UNT114$UNT166$UNT27$UNT60$UNT103$UNT58$
003049  175 UNT106$UNT54$UNT86$UNT87$UNT130$UNT131$UNT132$
003049  177 UNT51$UNT56$UNT91$UNT92$

and i need a query something like :

select * from T2 where empcode='abcd' unitcode in ('UNT111','UNT112','UNT113') 
//only that particular emps Unitcodes from the table T1

Possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Jorge Campos, Oct 14 '15 at 06:09
is other table is partially filled and you wanted to update the table? — wasipeer, Oct 14 '15 at 06:15
no i just want pull data from the other table with the first table info. @wasipeer — Shibin, Oct 14 '15 at 06:18
@JorgeCampos ya, i've seen but i'm looking for a select statement not a big function — Shibin, Oct 14 '15 at 06:19
@JorgeCampos can we have a query something like that and the result should be input for the IN clause of the main select statement ! — Shibin, Oct 14 '15 at 06:27
Yes, just subquery it and add your in statement on the outside. — Jorge Campos, Oct 14 '15 at 06:33
Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Evaldas Buinauskas, Oct 14 '15 at 06:52
thanks for the idea, i've tried that and found a that only the first unitcode is taking, the original query is as follows — Shibin, Oct 14 '15 at 06:57
select UNITCODE from T_UNIT_MSTR where UNITCODE in( select ParsedData.* from t_regionalhr_mstr mt cross apply ( select str = mt.unitcode + ',,' ) f1 cross apply ( select p1 = charindex( '$', str ) ) ap1 cross apply ( select p2 = charindex( '$', str, p1 + 1 ) ) ap2 cross apply ( select Nmame = substring( str, 1, p1-1 ) where empcode='002585' ) ParsedData ) — Shibin, Oct 14 '15 at 06:58

Stanislovas Kalašnikovas · Accepted Answer · 2015-10-14T07:00:27.840

You can do It in following:

QUERY

SELECT * 
FROM #test2
WHERE ID IN (
            SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Unitcodes
            FROM
                (
                SELECT CAST('<XMLRoot><RowData>' + REPLACE(Unitcodes,'$','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
                FROM   #test
                )t
            CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
            )

SAMPLE DATA

CREATE TABLE #test
(
   Empcode INT, 
   Eid INT,
   Unitcodes NVARCHAR(MAX)
)
INSERT INTO #test VALUES                                                                                           
(000559,  165, 'UNT109$UNT114$UNT166$UNT27$UNT60$UNT103$UNT58$'),
(003049,  175, 'UNT106$UNT54$UNT86$UNT87$UNT130$UNT131$UNT132$')

CREATE TABLE #test2
(
   ID NVARCHAR(MAX)
)

INSERT INTO #test2 VALUES
('UNT54'),('UNT130'),('UNT999')

OUTPUT

ID
UNT54
UNT130

score 0 · Answer 2 · answered Oct 14 '15 at 06:49

Create a function : This function will split your values

  Create FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter          varchar(20))
   RETURNS @Strings TABLE
  (   
     position int IDENTITY PRIMARY KEY,
     value varchar(8000)  
    )
  AS
  BEGIN

  DECLARE @index int
  SET @index = -1

      WHILE (LEN(@text) > 0)
     BEGIN 
   SET @index = CHARINDEX(@delimiter , @text) 
  IF (@index = 0) AND (LEN(@text) > 0) 
   BEGIN  
INSERT INTO @Strings VALUES (@text)
  BREAK 
END 
  IF (@index > 1) 
 BEGIN  
  INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
  SET @text = RIGHT(@text, (LEN(@text) - @index)) 
 END 
ELSE
 SET @text = RIGHT(@text, (LEN(@text) - @index))
END
 RETURN
 END

then write your query

select * from T2 where empcode='abcd' unitcode in (fn_Split("yourcomma seprated column",',')

score -1 · Answer 3 · answered Oct 14 '15 at 06:20

-1

Try this

select * from T2 where empcode='abcd' unitcode in ('%UNT111%','%UNT112%','%UNT113%')

answered Oct 14 '15 at 06:20

wasipeer

1,015
11
23

In statement is not to use as a LIKE statement. – Jorge Campos Oct 14 '15 at 06:21
just giving him an idea – wasipeer Oct 14 '15 at 06:23
Well, i need those values to be added dynamically. – Shibin Oct 14 '15 at 06:23
I need a select statement inside that, but that where i got stuck. is there a way with cross apply ?? – Shibin Oct 14 '15 at 06:24
you can use select statement – wasipeer Oct 14 '15 at 06:25
http://dba.stackexchange.com/questions/33553/using-a-select-in-a-where-clause-for-another-select – wasipeer Oct 14 '15 at 06:31

Comma separated values In An IN statement (SQL)

3 Answers3