Dynamic Like Clause 1

[briangriffin]

I need to pass in multiple values, and then execute a like statement for each value in the where clause:

Input values: '5','6','ICU'

and have the result resemble:

select * from units where unit like '%5%' or unit like '%6%' or unit like '%ICU%'

This is as far as I've gotten. The problem seems to be exiting the loop after the last value has been reached (and modifying this so that it still works if only one value is passed in).

-- Sample Data
drop table Units
create table Units
(unit varchar(10))

insert into units
select 'PEDS'
union
select '5E'
union
select '6W'
union
select 'ICU'
union
select 'NICU'

-- End Sample Data

declare @v_input varchar(50)
set @v_input = '6,IC,5'

declare @v_sql varchar(1000)

set @v_sql = 
'select * from units where unit like ''%'' + LEFT(''' + @v_input + ''',charindex('','',''' + @v_input + ''') - 1) + ''%'''
set @v_input = RIGHT(@v_input, len(@v_input) - charindex(',',@v_input))

while charindex(',',@v_input) > 0
BEGIN
set @v_input = RIGHT(@v_input, len(@v_input) - charindex(',',@v_input))
set @v_sql = @v_sql + ' or unit like ''%'' + LEFT(''' + @v_input + ''',charindex('','',''' + @v_input + ''') - 1) + ''%'''
END

--select @v_sql

exec(@v_sql)

There may be some set based solution that eliminates the need for dynamic SQL, if so even better.

Thanks in advance for your help.

 

[MegaSQLBI]

try this one

declare @v_input varchar(50)
declare @v_sql varchar(1000)

set @v_input = '6,IC,5'

Select @v_sql = 'Select * From Units Where Unit Like ''%' + REPLACE(@v_input, ',', '%'' Or Unit Like ''%') + '%'' '

Select @v_sql

Exec (@v_sql)

Mega

 

[briangriffin]

Perfect, thanks. But I'm not sure how it's generating multiple OR's. Reading this I would expect it to be:

... like '%6%5%ICU%'

If you have time can you explain what is going on? Thanks.

 

[djj55]

I think you missed the double single quotes. The REPLACE will replace all commas with %' Or Unit Like '%

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[MegaSQLBI]

It's replacing Comma's with %' Or Unit Like '% and adding necessary prefix & suffix to complete the syntax.



Meganathan