CODEWITHSUNDEEP
sqlsql-servert-sqlsql-like
ana ng
ana ng

Reputation: 107

An esoteric pondering regarding the lack of compatibility between % and = and <>

I am new to the world of programming but please humor me nonetheless.

I know that % works with LIKE and NOT LIKE. For example the following two queries work:

--QUERY 1
SELECT *
FROM TrumpFeccandid_Pacs
WHERE PACID NOT LIKE 'C%'

--QUERY 2
SELECT *
FROM TrumpFeccandid_Pacs
WHERE PACID LIKE 'C%'

However % does not work with = or <>. For example, the following two queries do not work:

--QUERY A
SELECT * 
FROM TrumpFeccandid_Pacs 
WHERE PACID <> 'C%'

--QUERY B
SELECT *
FROM TrumpFeccandid_Pacs
WHERE PACD = 'C%'

Why is this the case? Intuitively speaking I feel like not only should queries A and B work but Query A should be equivalent to Query 1 and Query B should be equivalent to Query 2.

These examples were using T-SQL from Sql Server 2016.

Upvotes: 0

Views: 44

Answers (3)

John Wu
John Wu

Reputation: 52290

Image a relatively simple query like this one:

SELECT *
FROM A
JOIN B ON A.Name = B.Name

If = worked like LIKE, god help you if Name contains a percent or underscore!

Upvotes: 1

James K. Lowden
James K. Lowden

Reputation: 7837

Intuitively speaking I feel like

That is where you go awry!

LIKE is defined a certain way, as are = and <>. The people who designed the language presumably tried to make it accessible, to make it easy to understand and remember and use. What they did not do, because they could not do, is define it such that it meets everyone's expectations and hunches.

Why is LIKE different from =?

  • a like 'C%' is true if a starts with 'C'
  • a = 'C%' is true if a is exactly the 2 letter string 'C%'

But the real moral to this story IMO is that if you want to know how the language works, the best advice is RTFM. Especially when it doesn't work as expected.

Upvotes: 1

Vidya Thotangare
Vidya Thotangare

Reputation: 13

SQL provides standard pattern matching like those used in Unix, grep, sed. These patters can be used only with operators "LIKE" and "NOT LIKE"..... LIKE/NOT LIKE are Boolean types i.e they returns TRUE/FALSE if the match_expression matches the specified pattern.

Following are various wild card used to match the patterns:

% = Any number of characters
_ = Any Single character 
[] = Any single character within the specified range 
[^] = Any single character not within the specified range

Documentation on patterns and like operators: SQL server LIKE operator

Upvotes: 0

Related Questions