I have a table in MySql that has a field called Codigo , of type varchar(20) , this field in this case accepts codes that have zero on the left and codes that do not have zero on the left.
Example:
Códigos 000123 e 321
When I do the SELECT this way:
SELECT * FROM Codigos WHERE Codigo LIKE '123'
It does not return me the code 000123 .
I would like to know how I can do this SELECT.
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 = and not LIKE .
One possible solution is:
SELECT * FROM Codigos WHERE CAST(Codigo AS UNSIGNED) = 123;
(use SIGNED if you have negative numbers)
Or even:
SELECT * FROM Codigos WHERE 0 + Codigo = 123
but this should not be used definitively, if your data is always numeric or null.
Note that you can create numeric columns with prefix 0 using the ZEROFILL option, which is one of the MySQL column options. In this case, you do not need to use quotation marks:
SELECT Codigo FROM Codigos WHERE Codigo = 123
Result, assuming the column is set to INT(6) ZEROFILL :
000123
More details on MySQL manual (in ) .
Try this code:
Select * from Codigos where Codigo LIKE '%123';
% Get zero or more characters
– Take only one character
Any questions please let me know, I hope I have helped.