Asif Ali
Asif Ali

Reputation: 73

What is the difference between = and LIKE

I am running a query on a column postal (type double).

SELECT * FROM `table` WHERE `postal` LIKE 'abcdef'; # returns 1 record

and the same query using = returns 100+ records.

SELECT * FROM `table` WHERE `postal` = 'abcdef'; # returns 107 record

What could be the reason?

Upvotes: 0

Views: 138

Answers (4)

Pred
Pred

Reputation: 9042

The following things affects the result (not the complete list!)

  • Implicit conversation
  • MySQL extension to standard SQL's LIKE operator

In each cases an implicit conversion occours: MySQL tries to convert the values to a common data type. In the first case case 'abcdef' will be converted to double which results to 0. This is why you get 107 records when comparing with equals (=).

SELECT * FROM `table` WHERE `postal` = 'abcdef'; # returns 107 record

You should get exactly the same result by running

SELECT * FROM `table` WHERE `postal` = 0;

In MySQL, LIKE is permitted on numeric expressions. (This is an extension to the standard SQL LIKE.)

This means that SELECT CASE WHEN 10 LIKE '1%' THEN 1 ELSE 0 END is allowed and results to 1 (matched)

To be honest, I'm not sure which double value could match with LIKE operator with the pattern 'abcdef'.

Upvotes: 0

Lilley
Lilley

Reputation: 234

You are using LIKE on a DOUBLE field, you should not do that.

LIKE is reserved for pattern matching on strings. Use = for numbers, or convert your digit to a string first using CONVERT and then apply the logic with LIKE.

Upvotes: 1

adarsh hota
adarsh hota

Reputation: 327

LIKE will check and return similar values where as = will check for the exact value.

Upvotes: 0

Thiyagu
Thiyagu

Reputation: 17880

= compares two values for identity. LIKE is for pattern matching ie. that is, it matches a string value against a pattern string containing wild-card characters.

Refer here

Upvotes: 0

Related Questions