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

Question

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!

Gordon Linoff · Accepted Answer · 2014-01-31 17:30:24Z

1

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

answered Jan 31, 2014 at 17:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related