Page 1 of 1
VARCHAR column sortable if numbers?
Posted: Fri Nov 18, 2005 6:38 pm
by seodevhead
I have a quick and easy question...
If I have a VARCHAR mysql column and all the rows in that column contain strings of numbers, like 10, 21, 33, 52, etc... if you were to sort this column would the numbers sort chronologically just like word strings would be sorted alphabetically??
example:
$a = '1';
$b = '3';
$c = '2';
then INSERT above variables into table in the same VARCHAR column
then SELECT varchar_column FROM table ORDER BY varchar_column ASC
output:
1
2
3
Would this be the output?
Posted: Fri Nov 18, 2005 6:43 pm
by lilleman
Yes, that will be the output for that data.
However, if you have the following data in your table:
... then the result will be this:
Posted: Fri Nov 18, 2005 6:51 pm
by seodevhead
oh wow... thanks so much.. I didnt think about that...
The problem I am running into is that I have a column that needs to only hold 5 values...
either of the following (user selects these options from drop-down):
<9
9
10
11
>11
Everything would be fine and dandy to use INT as my column type, but the '>' and '<' screw it up. So I thought I could use VARCHAR and still have it sort. I would also like to mathematical comparison operators in php for various tasks... such as..
if (number < 10) , etc.
Any suggestions on what I could do? Thanks so much for the help.
Posted: Fri Nov 18, 2005 8:40 pm
by Burrito
why does the db have to hold the "<"?
you could store it in an int field and add the extra chars at runtime.
the only way to sort the numbers correctly is to use an int field because as lilleman suggested, your sample would sort as:
<9
>11
10
11
9
(at least I think the < and > would be first (maybe last though)...in any case it wouldn't sort correctly).
Posted: Sat Nov 19, 2005 3:21 am
by lilleman
Since you know in which order you want the result to be sorted, you can use the FIELD function:
Code: Select all
SELECT * FROM your_table ORDER BY FIELD(field_name, '<9', '9', '10', '11', '>11')
Posted: Sat Nov 19, 2005 3:42 am
by m3mn0n
Moved topic to Databases forum.
Posted: Sat Nov 19, 2005 6:44 am
by Chris Corbyn
Use the MySQL CAST() function to have MySQL order numerically...
Code: Select all
SELECT
CAST(some_varchar_field AS int) AS something
WHERE
foo = bar
ORDER BY
something
OR....
Code: Select all
SELECT
field1,
field2
WHERE
foo = bar
ORDER BY
CAST(some_varchar_field AS int)