Search code examples
sqlsql-serversql-likelike-keyword

Why does LIKE not return rows for variables with '%' at end?

I find this quite odd on Microsoft SQL Server:

SELECT * FROM deliveries WHERE code LIKE '01999195000%'
-- 9 rows returned. Works.

DECLARE @a VARCHAR(10)
SET @a='01999195000%'
SELECT * FROM deliveries WHERE code LIKE @a
-- 0 rows returned? Why not?

SET @a = '01999195000'
SELECT * FROM deliveries WHERE code LIKE @a + '%'
-- 9 rows returned. Works.

What is different between searching for @a which includes the % character, and one that does not but has '%' appended?

If any of you SQL Guru's could share your thoughts, that would be great.

Solution

  • It's because you've defined @a as a VARCHAR(10), but you've tried putting 12 characters into it...meaning the "%" gets lost from the end