Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic Like Clause 1

Status
Not open for further replies.

briangriffin

Programmer
Nov 6, 2008
878
US

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).

Code:
-- 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.

 
try this one
SQL:
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
 

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.
 
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!
 
It's replacing Comma's with %' Or Unit Like '% and adding necessary prefix & suffix to complete the syntax.



Meganathan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top