Incorrect match with LIKE and parameter

Question

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 it?

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

Answer 1

This is a stupid problem. If you declare something as VARCHARin CAST, then it will automatically configure VARCHAR VARCHAR(30). In this case, however, you have one VARCHAR character. Therefore, when you set it to %Matching%because @Match- only ONE character is long, @Matchonly the character character %that matches this phrase (and any phrase!) Is set.

DECLARE @Match VARCHAR(50)

, .

:

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

Answer 2

varchar , varchar (1) . varchar (SIZE)