Comparing Numeric With Alpha Numeric

Question

I am running the following query

SELECT * FROM `student` WHERE '123sks'=123

And its returning true and I am confused why?

I am using 5.7.24 - MySQL Community Server (GPL)

What should i do if i want to comape '123sks' with 123 so that it results in false.

Possible duplicate of MySQL automatic string to integer casting in where clause? — Solarflare
– Solarflare, Commented Apr 14, 2019 at 9:14

forpas · Accepted Answer · 2019-04-14 10:07:13Z

3

From: Type Conversion in Expression Evaluation

When an operator is used with operands of different types,
type conversion occurs to make the operands compatible.
Some conversions occur implicitly.

To get the idea of the how MySql makes these type conversions try these explicit conversions:

SELECT 
  cast('123abc' as unsigned),
  cast('1abc23' as unsigned),
  cast('abc123' as unsigned)

will have this result:

| col1 | col2 | col3 |
| ---- | ---- | ---- |
| 123  | 1    | 0    |

You may have guessed that for any string that you try to convert to an integer,
the result is the integer value that consists of the starting numerical chars of the string if there are any, or 0 if there are not.
So in your case '123sks' is converted (implicitly) to 123.
To get FALSE from the comparison, convert 123 to string:

WHERE '123sks' = cast(123 as char(10))

edited Apr 14, 2019 at 10:07

answered Apr 14, 2019 at 9:16

forpas

165k10 gold badges51 silver badges85 bronze badges

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

3 Comments

shivanshu dixit Over a year ago

What should i do if i want to comape '123sks' with 123 so that it results in false.

forpas Over a year ago

You can't get false in this case. MySql does an implicit conversion when you do a straight comparison. But why should you compare a string with a number?

shivanshu dixit Over a year ago

thanks for answering i was just trying to compare an auto increment value with a form submit <input type="text"> but now i will be using <input type="number">

Collectives™ on Stack Overflow

Comparing Numeric With Alpha Numeric

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related