Link to home
Start Free TrialLog in
Avatar of breeze351
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?
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

STORENUM shouldn't be VARCHAR. You are getting results based on a string sort.

It should be:
Integer       Allows whole numbers between -32,768 and 32,767       2 bytes
After that, you can run the query as follows
SELECT * FROM survey_data WHERE STOREKEY < 999
SELECT * FROM survey_data WHERE STORENUM < 999

Open in new window

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

SELECT * FROM survey_data WHERE LENGTH(STOREKEY) < 4;

Open in new window


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
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of breeze351
breeze351

ASKER

Thanks
You are welcome.