Search code examples
sql-serversqlparameter

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
Solution

  • 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