Trying to use SQL Replace in a non-dynamic way

Question

I have the following statement, where I'm passing in a parameter like this:

'0001,0003'

I was following the REPLACE answer from this question: SQL IN Statement splitting parameter

But I'm trying to take it out of dynamic sql. My returned result is NULL. Is there anyway to get this to work?

DECLARE @partialLNum varchar(MAX)       
DECLARE @lNumConCat varchar(500)
DECLARE @tTemp table(lNum varchar(15))
DECLARE @formatIN varchar(MAX)

set @partialLNum = '0001,0003'
set @formatIN = ''''+ REPLACE(@partialLNum,',',''',''')+'''' 


        insert into @tTemp
        select substring(lNum,1,2) + '-' + substring(lNum,3,3) + '-' + substring(lNum,6,2) + '-' + substring(lNum,8,3)
        from  [rpt].[myView] 
        where LNum IN (@formatIN)


        select @lNumConCat =  COALESCE(@lNumConCat +'' , '', '''') + LNum from @tTemp
        select @lNumConCat

did you see http://stackoverflow.com/a/23438993/3664960 – davejal Dec 05 '15 at 02:26 — davejal, Dec 05 '15 at 02:26

score 1 · Accepted Answer · answered Dec 05 '15 at 03:14

in takes a list of values. So:

    where LNum IN ('0001,0003')

has a list with one element, that happens to have a comment in it.

One way to do what you want is using like:

where ',' + partialLNum + ',' like '%,' + LNum + ',%'

There should suffice, but there are other ways using a split() function as well.

Trying to use SQL Replace in a non-dynamic way

1 Answers1