CODEWITHSUNDEEP
mysql
Vigintas Labakojis
Vigintas Labakojis

Reputation: 1069

mysql only compares first numerical part of string to int column in where

This is more a "why" or "any pointers to documentations" question.

Today I realised that a comparison in WHERE clause takes only the first numerical part of the string if compared to an INT column. (My own conclusion)

For example:

SELECT * FROM companies c WHERE id = '817m5'

will still return companies with id = 817.

It does make sense but I'm failing to find this documented anywhere to see if my conclusion is correct and if there are additional things to know about this exact behaviour. WHERE, INT type, comparison documentation? Where? How is it called?

Thanks!

Upvotes: 0

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is the comparison:

WHERE id = '817m5'

and id is an integer.

So, MySQL has to compare an integer to a string. Well, it can't do that directly. It either has to convert the string to a number or the number to the string. MySQL converts the string to a number, which it does by converting the leading numeric characters. So, the '817m5' becomes 817.

Here is the exact quote:

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

mysql> SELECT 1+'1'; -> 2

Upvotes: 1

Related Questions