CODEWITHSUNDEEP
sqlsql-serversql-likelike-keyword
Simon Hughes
Simon Hughes

Reputation: 3574

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.

Upvotes: 1

Views: 358

Answers (3)

Mr. Smith
Mr. Smith

Reputation: 5558

DECLARE @a VARCHAR(10) is the answer. @a never contains the %.

Upvotes: 3

Rik Heywood
Rik Heywood

Reputation: 13972

LIKE is a wildcard character, meaning "anything you like here".

Upvotes: -2

AdaTheDev
AdaTheDev

Reputation: 147344

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

Upvotes: 27

Related Questions