4

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
James
  • 80,725
  • 18
  • 167
  • 237
Arnoud Kooi
  • 1,588
  • 4
  • 17
  • 25

2 Answers2

5

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
Eli Gassert
  • 9,745
  • 3
  • 30
  • 39
  • It doesn't "autosize" it if no length is specified in a cast. It uses `VARCHAR(30)` rather than `varchar(1)` though. – Martin Smith Nov 05 '13 at 13:40
  • @MartinSmith the default value is 1 for the (n)char and (n)varchar data types when they are used in variable declaration – t-clausen.dk Nov 05 '13 at 14:50
  • 1
    @t-clausen.dk - Yes I know. The point I was correcting was the sentence `If you declare something as VARCHAR in a CAST then it auto-sizes the VARCHAR properly`. This is not the case. `CAST(X AS VARCHAR)` will cause an error or truncation if `X` is longer than 30 characters. – Martin Smith Nov 05 '13 at 14:51
  • +1 I thought it auto-sized and did NOT know it defaulted to 30. I always wondered how it calculated the size :) Fixing my answer, thank you! – Eli Gassert Nov 05 '13 at 18:10
1

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

Sahil Sareen
  • 1,813
  • 3
  • 25
  • 40