How can i use the replace statement in query

Question

I am trying to query a table that has values separated by commas as follows:

SELECT ID, NAME,FULLNAME,STATUS,STORE 
FROM EMPLOYEE 
WHERE STORE IN (SELECT '''' + REPLACE('001,002',',',''',''') +'''')
ORDER BY STORE

when I run the query above, it produces no results, but when I run it like this:

SELECT ID, NAME,FULLNAME,STATUS,STORE 
FROM EMPLOYEE 
WHERE STORE IN ('001','002')
ORDER BY STORE

I get like 500 records.

And when I try this:

SELECT ('''' + REPLACE('001,002',',',''',''') +'''')

I get the result '001','002'

so my question is, why the first script does not work, and produces no results?

Is there something I must add to the script for it to work?

please advise.

Let's back up a step. Why are you trying to build the string of `store` values like that? There's probably a better way. — Joe Stefanelli, Apr 17 '15 at 20:57
Your design is problematic and will continue to cause you issues. Please stop treating SQL Server like a JSON utility. If you want to pass a set of values to check against, use a table-valued parameter. This string parsing nonsense is, well, nonsense. — Aaron Bertrand, Apr 17 '15 at 21:00
possible duplicate of [Parameterize a SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause) — Andriy M, Apr 19 '15 at 08:55

score 0 · Answer 1 · edited Apr 21 '15 at 04:36

What if I had this scenario

SELECT ID, NAME,FULLNAME,STATUS,STORE 
FROM dbo.EMPLOYEE 
WHERE STORE IN (
    SELECT t2.ID
    FROM (
        SELECT Value = REPLACE('001,002', ',', '.')
    ) t
    CROSS APPLY (
        VALUES 
            (PARSENAME(t.Value, 1)),
            (PARSENAME(t.Value, 2))
    ) t2 (ID)
)
AND STATUS IN (
    SELECT t2.ID1
    FROM (
        SELECT Value1 = REPLACE('A,T,L', ',', '.')
    ) t1
    CROSS APPLY (
        VALUES 
            (PARSENAME(t1.Value1, 1)),
            (PARSENAME(t1.Value1, 2))
    ) t2 (ID1)
)

ORDER BY STORE

I tried that, and it didn't work, so I am just wondering if it works for more than 1 condition.

How can i use the replace statement in query

1 Answers1