Using Like on a Parameter Variable?

Question

Select Column1 From Table Where @Variable Like '%' + Column2 + '%'

Doesn't seem to work how I thought it would. Any suggestions?

Well, It doesn't work like Where Column2 Like '%' + @Variable + '%' — Control Freak
– Control Freak, Commented Jun 22, 2012 at 8:37
It's not a commutative function. What is in your columns, variables, etc? — podiluska
– podiluska, Commented Jun 22, 2012 at 8:44
What do you mean, "doesn't work like" that? The two are different comparisons; naturally they won't necessarily have the same results. Show a specific example of where you get a match where you wouldn't expect one (or vice versa). — AakashM
– AakashM, Commented Jun 22, 2012 at 8:45
Ok, well since you know the issue, feel free to post your answer below. ;) — Control Freak
– Control Freak, Commented Jun 22, 2012 at 8:46

whytheq · Accepted Answer · 2012-06-22 09:05:20Z

6

(vague question)

Have you got Category and @Variable round the wrong way: sqlFiddle

create table the_table 
(
  category varchar(10),
  [Date] datetime,
  Amount decimal(12, 2)
)

insert into the_table
values
( 'X', '2012-1-1', 10),
( 'X', '2012-1-3', 10),
( 'Y', '2012-1-3', 20),
( 'Y', '2012-1-5', 10)

declare @Variable varchar(10)
set @Variable = 'Y'

Select * 
From the_table 
--Where @Variable Like '%' + category + '%' 
Where category Like '%' + @Variable + '%'

answered Jun 22, 2012 at 9:05

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Control Freak Over a year ago

Hmmm.. i suppose i can put the variable in a table first.. let me try.

Andomar · Accepted Answer · 2012-06-22 08:33:16Z

1

If you have a limited number of columns, you can use case:

where  case 
       when @Variable = 'col1' then col1 
       when @Variable = 'col2' then col2
       when @Variable = 'col3' then col3 
       ...
       end like '%' + Column2 + '%'

Another option is dynamic SQL:

declare @sql nvarchar(max)
set @sql = 'Select Column1 From Table Where ' + @Variable + 
    ' Like ''%'' + Column2 + ''%'''
exec (@sql)

answered Jun 22, 2012 at 8:33

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Control Freak Over a year ago

Unfortunately Variable is not a value from a column, it's actually from a param. So i guess i will update the question title. Your 2nd example, no no no, please.

Collectives™ on Stack Overflow

Using Like on a Parameter Variable?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related