CODEWITHSUNDEEP
phpmysql
Janp
Janp

Reputation: 55

How to select true value when combining alphanumeric?

mysql: customer_number = int (cannot be changed)

$query = "SELECT * FROM clients WHERE customer_number= '123F'"

The RESULT should be empty.

The query has a result which is incorrect because, MySQL ignore any alphabet character which form part of the value with an integer datatype, eg.'F'

Upvotes: 0

Views: 74

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Or check as first condition if the integer from the string equal the string (change the string to your fieldname) :

SELECT * FROM clients 
WHERE
  '123F'+0 = '123F' 
AND
  customer_number= '123F';

Upvotes: 0

Pupil
Pupil

Reputation: 23958

You can check if the value provided is strictly integer.

$query = "SELECT * FROM clients WHERE customer_number= '123F' 
AND '123F' REGEXP '^-?[0-9]+$'";

AND needs both conditions compulsorily.

So, even if MySQL ignores F from 123F and treats it as 123, second condition will return FALSE and 123F will fail regular expression of strictly integer condition.

This will have following results:

123 -> pass

123F -> fail

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can cast customer_number to string:

SELECT *
FROM clients
WHERE CAST(customer_number as CHAR(50)) = '123F'

The above query is of course not SARGable. Consider processing the comparison argument on the client side before the construction of the query.

Demo here

Upvotes: 0

Related Questions