CODEWITHSUNDEEP
sql-serversqlparameter
Arnoud Kooi
Arnoud Kooi

Reputation: 1767

Wrong match with LIKE and parameter

I want to compare a string to see if it contains a substring, however, when I use a variable it evaluates to true when it should be false.

Any idea why this is happening and how to fix this?

DECLARE @Match VARCHAR
SET @Match = '%Matching%'

SELECT CASE WHEN 'Does This Match' LIKE @Match THEN 1 ELSE 0 END -- 1
SELECT CASE WHEN 'Does This Match' LIKE '%Matching%' THEN 1 ELSE 0 END -- 0

Upvotes: 4

Views: 211

Answers (2)

Eli Gassert
Eli Gassert

Reputation: 9763

It's a silly issue. If you declare something as VARCHAR in a CAST then it auto-sizes the VARCHAR properly to VARCHAR(30). In this case, though, you have a single VARCHAR character. So when you set it to %Matching% because @Match is only ONE character long, @Match gets set to just the wildcard character % which DOES match that phrase (and any phrase!).

DECLARE @Match VARCHAR(50)

Do that, then works.

Full example:

DECLARE @BadMatch VARCHAR
SET @BadMatch = '%Matching%'


DECLARE @Match VARCHAR(20)
SET @Match = '%Matching%'

SELECT @BadMatch, @Match

SELECT CASE WHEN 'Does This Match' LIKE @Match THEN 1 ELSE 0 END -- 1
SELECT CASE WHEN 'Does This Match' LIKE '%Matching%' THEN 1 ELSE 0 END -- 0

Upvotes: 5

Sahil Sareen
Sahil Sareen

Reputation: 1834

varchar will not work because that would be equivalent to varchar(1) one character wide. Use varchar(SIZE)

Upvotes: 1

Related Questions