Problem matching variables with Like statement

Question

Post reply

Problem matching variables with Like statement

nano2nd

Hall of Fame

Points: 3073
More actions
August 23, 2011 at 9:54 am

#256123

I have a table that has user defined masks in it. For example, record 33 has the following columns
id 1
Mask A 1%
The user inputs a value and I want to compare it to the mask using a Like statement. If the user inputs
'A 104' I expect a match, but it doesn't work. Trivial sample code below
create table MaskTest
( id int, Mask char(7))
insert MaskTest (Mask) values ('A 1%')
declare @grade Char(7)= 'A 104'
declare @mask char(7)
select @mask = Mask from MaskTest where id=1
if @grade like @mask
begin
select 'match'
end

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Neal Sivley SSCommitted Points: 1766 More actions · Answer 1

Try this. Don't put the % sign in the variable

WHERE Column1 like '%' + @Variable + '%'

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

nano2nd (8/23/2011)
I have a table that has user defined masks in it. For example, record 33 has the following columns
id 1
Mask A 1%
The user inputs a value and I want to compare it to the mask using a Like statement. If the user inputs
'A 104' I expect a match, but it doesn't work. Trivial sample code below
create table MaskTest
( id int, Mask char(7))
insert MaskTest (Mask) values ('A 1%')
declare @grade Char(7)= 'A 104'
declare @mask char(7)
select @mask = Mask from MaskTest where id=1
if @grade like @mask
begin
select 'match'
end

Actually the reason you are not finding a match is not the location of the % but you are trying to match on char fields. Which means it is trying to find a match including the spaces.

create table MaskTest

( id int identity, Mask char(7))

insert MaskTest (Mask) values ('A 1%')

declare @grade Char(7)= 'A 104'

declare @mask char(7)

select @mask = Mask from MaskTest where id=1

select @mask as Mask, @grade as grade --copy and paste these values and you will see the spaces.

if @grade like rtrim(@mask)

begin

select 'match'

end

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nano2nd Hall of Fame Points: 3073 More actions · Answer 3

I sort of need the % sign in the variable because users will be maintaining the table. They will be able to add other masks with wildcards. I may be able to get around this by adding a column for MatchType where MatchType would be either 'Equal' or 'Like'.

I tried this but it didn't work:

create table MaskTest

(

id int,

Mask char(7)

)

insert MaskTest (Mask) values ('A 1')

declare @grade varChar(7)= 'A 104'

declare @mask varchar(7)

select @mask = Mask from MaskTest where id=1

select @mask from MaskTest where @grade like (Mask + '%')

nano2nd Hall of Fame Points: 3073 More actions · Answer 4

nano2nd

Hall of Fame

Points: 3073

August 23, 2011 at 10:35 am

#1372148

Thanks, that makes sense and works fine.