CODEWITHSUNDEEP
mysql
Mikhail Knyazev
Mikhail Knyazev

Reputation: 101

MySQL: Searching number column with string parameter

Let's say we have a record in table 'orders' with id=1. This query:

SELECT * FROM 'orders' WHERE id = 'abc1'

won't return mentioned record. But this query:

SELECT * FROM 'orders' WHERE id = '1abc'

will return the record with id=1. This is because when MySQL converts string to number 'abc1' becomes 0, but '1abc' becomes 1. Is there a nice way to make MySQL search strictly for records with id from query, i.e. not return the record with id=1 in both mentioned cases?

Upvotes: 3

Views: 229

Answers (2)

Bassem Shahin
Bassem Shahin

Reputation: 706

I handled it with PHP before submitting the SQL query

$idTest = '1abc';
if (is_numeric($id){
    $query = "SELECT * FROM 'orders' WHERE id = '$idTest'"
}

This will prevent submitting queries if the $idTest has a string

Upvotes: 0

RelevantUsername
RelevantUsername

Reputation: 1340

What about using :

SELECT * FROM 'orders' WHERE id LIKE '1abc' COLLATE utf8_bin

or even

SELECT * FROM 'orders' WHERE STRCMP(id, '1abc') = 0;

Upvotes: 6

Related Questions