Does the Mysql Select command differentiate numbers that have zero on the left?

Asked

Viewed 321 times

2

I have a table on MySql which has a field called Codigo, of the kind varchar(20), this field in the case accepts codes that have zero left and codes that do not have zero to the left.

Example: 

Códigos 000123 e 321

When I do SELECT that way:

SELECT * FROM Codigos WHERE Codigo LIKE '123'

He won’t return the code 000123. I would like to know how I can do this SELECT.

php mysql
  • 1

    The real problem is that you are writing numbers as text (varchar). In text, "1" and "01" are different.

  • 1

    SELECT * FROM Codigos WHERE 0 + Codigo = 123 - is to "fix" the problem @bfavaretto has already mentioned. The correct thing is to fix the DB. If you need zeros, control in the view instead of storing.

  • Thank you, in case I’ll arrange in the comic then, since I only have to store whole numbers, and in case I standardize not to be used the Zero left! so it’s easier for me to treat later.

2 answers

7


The problem, as mentioned by @bfavaretto in the comments, is that you are probably storing your numerical data as strings.

In addition, if you are looking for exact values, the operator is the = and not the LIKE.

A possible solution is:

SELECT * FROM Codigos WHERE CAST(Codigo AS UNSIGNED) = 123;

(use SIGNED if it has negative numbers)

Or even:

SELECT * FROM Codigos WHERE 0 + Codigo = 123

but this should not be used definitively, if your data is always numerical or null.


ZEROFILL

Note that you can create numeric columns with prefix 0 using the option ZEROFILL, is one of the Mysql column options. In this case, just don’t use quotes:

SELECT Codigo FROM Codigos WHERE Codigo = 123

Result, assuming the column is defined as INT(6) ZEROFILL:

000123

More details on mysql manual.

  • 2

    Thank you, in case I’ll arrange in the comic then, since I only have to store whole numbers, and in case I standardize not to be used the Zero left! so it’s easier for me to treat later.

1

Try this code:

Select * from Codigos where Codigo LIKE '%123';

% Takes zero or more characters

– Takes only one character

Any further questions let me know, I hope I’ve helped.

  • Thank you, in case I’ll arrange in the comic then, since I only have to store whole numbers, and in case I standardize not to be used the Zero left! so it’s easier for me to treat later.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.