0

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?

Eduard Uta
  • 2,477
  • 5
  • 26
  • 36
Asif Ali
  • 63
  • 1
  • 5
  • Try `LIKE 'a%'` and `= 'a%'` you will see the difference. Also note that you need a String field. I guess that's why you get these strange results – Sander Visser Jan 16 '15 at 07:54
  • see following link http://stackoverflow.com/q/1003381/2460470 – Supravat Mondal Jan 16 '15 at 07:56
  • 1
    Can you please tell us which version (exact version) of mysql are you using? Please note that `Before MySQL 5.5.3, an implicit conversion always produced a binary string, regardless of the connection character set.` – Pred Jan 16 '15 at 08:25
  • What is the value in `postal` for the first quey which matched to 'abcdef'? – Pred Jan 16 '15 at 08:52

4 Answers4

1

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.

Lilley
  • 214
  • 1
  • 5
0

= 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

Community
  • 1
  • 1
Thiyagu
  • 17,362
  • 5
  • 42
  • 79
0

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

adarsh hota
  • 327
  • 11
  • 23
0

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'.

Pred
  • 8,789
  • 3
  • 26
  • 46