VARCHAR column sortable if numbers?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

VARCHAR column sortable if numbers?

Post 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?
lilleman
Forum Newbie
Posts: 7
Joined: Thu Nov 17, 2005 4:55 pm
Location: Örebro, Sweden

Post by lilleman »

Yes, that will be the output for that data.

However, if you have the following data in your table:

Code: Select all

1
3
13
2
... then the result will be this:

Code: Select all

1
13
2
3
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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).
lilleman
Forum Newbie
Posts: 7
Joined: Thu Nov 17, 2005 4:55 pm
Location: Örebro, Sweden

Post 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')
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Moved topic to Databases forum.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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)
Post Reply