CODEWITHSUNDEEP
phpmysql
qadeerkhan
qadeerkhan

Reputation: 95

How to ignore the leading zeros in mysql?

While using the query:

select c_name from country where c_code_numeric='004';

it returns the correct result.

But with this:

select c_name from country where c_code_numeric='4';

returns an empty result

My questions are

c_code_numeric is VARChar.

This one works fine for me in both cases of 004 or just 4:

select c_name from country where c_code_numeric +0='4';

Upvotes: 0

Views: 960

Answers (3)

fthiella
fthiella

Reputation: 49089

If c_code_numeric is a VARCHAR that contains a number, you could use this query:

SELECT c_name
FROM country
WHERE c_code_numeric=4

that will cast c_code_numeric to a number, or you might also want to try this:

SELECT c_name
FROM country
WHERE c_code_numeric+0=4

Upvotes: 2

Arun Killu
Arun Killu

Reputation: 14263

you can use mysql cast function to explicit cast

select c_name from country where CAST(c_code_numeric AS UNSIGNED) = 4

Upvotes: 0

Bora
Bora

Reputation: 10717

You can prepend leading zero with LPAD

SELECT c_name FROM country WHERE c_code_numeric = LPAD('4', 3, '0')

Usage

LPAD(value, lenght, start_with)

Examples:

LPAD('4', 3, '0')   // 004
LPAD('14', 3, '0')  // 014
LPAD('114', 3, '0') // 114

Upvotes: 0

Related Questions