breeze351
asked on
Sql query on a varchar that is numeric.
I'm trying to clean up some data. I have a field called "STORENUM", it is defined as a varchar 4. The data should be like this "0100" or "2001". Some of the records look like "102" or "400". This is garbage that I want to get rid of. The problem is I wrote the following query:
SELECT * FROM `survey_data` WHERE `STOREKEY` < "999"
I thought that this would give me anything that was less than "1000", but I'm getting records where the "STORENUM" is "3800", "9800", "2401". Why?
SELECT * FROM `survey_data` WHERE `STOREKEY` < "999"
I thought that this would give me anything that was less than "1000", but I'm getting records where the "STORENUM" is "3800", "9800", "2401". Why?
If you use INT for the data type, expect the leading zeros to be lost. If you need leading zeros, you should treat the column as a character string. And (of course) beware of PHP loose typing which can readily find ways to lose leading zeros.
Just use the LENGTH() function
As Shaun pointed out - your fields are strings so are being sorted Lexicographically. That means "10000" comes before "9" because "1" comes before "9" when comparing strings.
SELECT * FROM survey_data WHERE LENGTH(STOREKEY) < 4;
As Shaun pointed out - your fields are strings so are being sorted Lexicographically. That means "10000" comes before "9" because "1" comes before "9" when comparing strings.
If you need leading zeros you can use padding function in PHP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have a field called "STORENUM"
the data type of that column should be the same as the primary key of the master table of STORES (assuming you have such a table)
if you change your survey_data column make sure you can still join to the relevant master table, without implicit type conversion
ASKER
Thanks
You are welcome.
It should be:
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
After that, you can run the query as follows
Open in new window