Professional Web Applications Themes

Sorting text with Numbers in a MYSQL query - MySQL

I have a mixture of text and numbers... for example Anthony Matthew 12 111 45 66 Gregory Jones I want to be able to sort so that it does the numbers first in numerical order and then the letters. I have looked on the net but can't find anything. Thanks...

  1. #1

    Default Sorting text with Numbers in a MYSQL query

    I have a mixture of text and numbers... for example


    Anthony
    Matthew
    12
    111
    45
    66
    Gregory
    Jones

    I want to be able to sort so that it does the numbers first in
    numerical order and then the letters.

    I have looked on the net but can't find anything.

    Thanks

    Anthony Guest

  2. #2

    Default Re: Sorting text with Numbers in a MYSQL query


    Anthony Ezkinazi wrote: 

    There may be a simpler way, but something like this would probably
    work:

    SELECT field FROM table ORDER BY CONCAT(10000-field,field) DESC;

    If you do it this way, just make sure that the concat number is higher
    than any of the numerical values

    strawberry Guest

  3. #3

    Default Re: Sorting text with Numbers in a MYSQL query

    Anthony Ezkinazi wrote:
     

    A simply ORDER BY will provide this (as the numeric 'characters' are earlier in the
    ASCII sequence than the alphabetical characters).

    SELECT <column> FROM <table> ORDER BY <column>;

    --

    Murdoc Guest

  4. #4

    Default Re: Sorting text with Numbers in a MYSQL query


    Murdoc wrote: 
    >
    > A simply ORDER BY will provide this (as the numeric 'characters' are earlier in the
    > ASCII sequence than the alphabetical characters).
    >
    > SELECT <column> FROM <table> ORDER BY <column>;
    >
    > --[/ref]
    But surely a simple ORDER BY will sort the numbers as characters rather
    than numerically.
    The OP wants 45 to come before 111.

    Captain Guest

  5. #5

    Default Re: Sorting text with Numbers in a MYSQL query

    On 20 Oct 2006 03:48:44 -0700, "Captain Paralytic"
    <com> wrote:
     
    >>
    >> A simply ORDER BY will provide this (as the numeric 'characters' are earlier in the
    >> ASCII sequence than the alphabetical characters).
    >>
    >> SELECT <column> FROM <table> ORDER BY <column>;
    >>
    >> --[/ref]
    >But surely a simple ORDER BY will sort the numbers as characters rather
    >than numerically.
    >The OP wants 45 to come before 111.[/ref]


    I may be wrong (have been b4), but I'm reasonably sure that MySQL will
    column table data based on the column "type", not the column
    "content".

    Lee
    Lee Guest

  6. #6

    Default Re: Sorting text with Numbers in a MYSQL query

    Lee Peedin wrote: 
    >>
    >>But surely a simple ORDER BY will sort the numbers as characters rather
    >>than numerically.
    >>The OP wants 45 to come before 111.[/ref]
    >
    >
    >
    > I may be wrong (have been b4), but I'm reasonably sure that MySQL will
    > column table data based on the column "type", not the column
    > "content".
    >
    > Lee[/ref]

    Yep, and the column is a character type. So 111 will be listed before
    45, which is not what the op wants.

    And it won't help to cast to int because that will place non-numeric
    strings (value of 0) ahead of numeric strings with a value > 0.

    This one's tougher - mainly because of the design of mixing numeric data
    with non-numeric data. Gonna have to play with it.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Sorting text with Numbers in a MYSQL query

    On Fri, 20 Oct 2006 07:53:18 -0400, Jerry Stuckle
    <net> wrote:
     
    >>
    >>
    >>
    >> I may be wrong (have been b4), but I'm reasonably sure that MySQL will
    >> column table data based on the column "type", not the column
    >> "content".
    >>
    >> Lee[/ref]
    >
    >Yep, and the column is a character type. So 111 will be listed before
    >45, which is not what the op wants.
    >
    >And it won't help to cast to int because that will place non-numeric
    >strings (value of 0) ahead of numeric strings with a value > 0.
    >
    >This one's tougher - mainly because of the design of mixing numeric data
    >with non-numeric data. Gonna have to play with it.[/ref]

    I'm very new to MySQL (in fact any SQL), but the only way we were able
    to solve such an issue with our current database was to "pad" such
    contents with leading zeros (0). That will "right align" all contents
    and numeric data will "appear" to be in the correct order - it would
    probably even work to pad "all" contents with leading spaces. (??)

    Lee
    Lee Guest

  8. #8

    Default Re: Sorting text with Numbers in a MYSQL query

    On 20 Oct 2006 03:48:44 -0700, Captain Paralytic wrote: 
    >>
    >> A simply ORDER BY will provide this (as the numeric 'characters' are earlier in the
    >> ASCII sequence than the alphabetical characters).
    >>
    >> SELECT <column> FROM <table> ORDER BY <column>;
    >>
    >> --[/ref]
    > But surely a simple ORDER BY will sort the numbers as characters rather
    > than numerically.
    > The OP wants 45 to come before 111.[/ref]

    Also cht-dependant. And possibly case-dependant.

    And you REALLY don't want to see what happens if you happen to move that
    off of MySQL someday and try putting it on DB2 system running on Big
    Iron...

    --
    The pig is nothing but a giant dish which walks while waiting to be served.
    --Grimod de La Reynière
    Peter Guest

  9. #9

    Default Re: Sorting text with Numbers in a MYSQL query

    Captain Paralytic wrote:
     
    > >
    > > A simply ORDER BY will provide this (as the numeric 'characters' are earlier in the
    > > ASCII sequence than the alphabetical characters).
    > >
    > > SELECT <column> FROM <table> ORDER BY <column>;
    > >
    > > --[/ref]
    > But surely a simple ORDER BY will sort the numbers as characters rather
    > than numerically.
    > The OP wants 45 to come before 111.[/ref]

    Of course. My mistake.

    --

    Murdoc Guest

  10. #10

    Default Re: Sorting text with Numbers in a MYSQL query

    Anthony Ezkinazi wrote: 

    I've played with this for quite a while, and haven't been able to come
    up with a good way to do it. Padding your fields with blanks will work
    for the numbers, but not the letters.

    A lot of your problem is your schema. It seems this column means two
    different things - as a number or a word. That's not good
    normalization, and you'd be better off putting it into a numeric and a
    char column.

    Any time you have both numeric and character data in the same column is
    reason to at least reexamine your schema.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  11. #11

    Default Re: Sorting text with Numbers in a MYSQL query

    >I have a mixture of text and numbers... for example 


    mysql> desc t;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | a | varchar(30) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.02 sec)

    mysql> select * from t;
    +---------+
    | a |
    +---------+
    | Anthony |
    | Matthew |
    | 12 |
    | 111 |
    | 45 |
    | 66 |
    | Gregory |
    | Jones |
    +---------+
    8 rows in set (0.00 sec)

    mysql> select a from
    -> (select a from t
    -> where a regexp '[0-9]'
    -> order by (a+0)) t1
    -> union
    -> select a from
    -> (select a from t
    -> where a regexp '[a-zA-Z]'
    -> order by a) t2;
    +---------+
    | a |
    +---------+
    | 12 |
    | 45 |
    | 66 |
    | 111 |
    | Anthony |
    | Gregory |
    | Jones |
    | Matthew |
    +---------+
    8 rows in set (0.01 sec)


    Regards
    Dimitre


    Radoulov, Guest

  12. #12

    Default Re: Sorting text with Numbers in a MYSQL query

    > >I have a mixture of text and numbers... for example 
    >
    >
    > mysql> desc t;
    > +-------+-------------+------+-----+---------+-------+
    > | Field | Type | Null | Key | Default | Extra |
    > +-------+-------------+------+-----+---------+-------+
    > | a | varchar(30) | YES | | NULL | |
    > +-------+-------------+------+-----+---------+-------+
    > 1 row in set (0.02 sec)
    >
    > mysql> select * from t;
    > +---------+
    > | a |
    > +---------+
    > | Anthony |
    > | Matthew |
    > | 12 |
    > | 111 |
    > | 45 |
    > | 66 |
    > | Gregory |
    > | Jones |
    > +---------+
    > 8 rows in set (0.00 sec)
    >
    > mysql> select a from
    > -> (select a from t
    > -> where a regexp '[0-9]'
    > -> order by (a+0)) t1
    > -> union
    > -> select a from
    > -> (select a from t
    > -> where a regexp '[a-zA-Z]'
    > -> order by a) t2;
    > +---------+
    > | a |
    > +---------+
    > | 12 |
    > | 45 |
    > | 66 |
    > | 111 |
    > | Anthony |
    > | Gregory |
    > | Jones |
    > | Matthew |
    > +---------+
    > 8 rows in set (0.01 sec)[/ref]

    But ...


    mysql> insert into t values ('Studio1');
    Query OK, 1 row affected (0.04 sec)

    mysql> select a from
    -> (select a from t
    -> where a regexp '[0-9]'
    -> order by (a+0)) t1
    -> union
    -> select a from
    -> (select a from t
    -> where a regexp '[a-zA-Z]'
    -> order by a) t2;
    +---------+
    | a |
    +---------+
    | Studio1 |
    | 12 |
    | 45 |
    | 66 |
    | 111 |
    | Anthony |
    | Gregory |
    | Jones |
    | Matthew |
    +---------+
    9 rows in set (0.00 sec)

    So,
    it would be:

    mysql> select a from
    -> (select a from t
    -> where a regexp '^[[:digit:]]+$'
    -> order by (a+0)) t1
    -> union
    -> select a from
    -> (select a from t
    -> where a regexp '[[:alnum:]]'
    -> order by a) t2;
    +---------+
    | a |
    +---------+
    | 12 |
    | 45 |
    | 66 |
    | 111 |
    | Anthony |
    | Gregory |
    | Jones |
    | Matthew |
    | Studio1 |
    +---------+
    9 rows in set (0.00 sec)


    Regards
    Dimitre



    Radoulov, Guest

Similar Threads

  1. Query of Queries is sorting when I don't want it to
    By angelseyeinc in forum Coldfusion Database Access
    Replies: 5
    Last Post: July 13th, 08:48 PM
  2. Sorting a Query from CF Directory
    By murpg in forum Macromedia ColdFusion
    Replies: 3
    Last Post: July 15th, 01:59 PM
  3. sorting library of congress numbers
    By Rick Schumeyer in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 20th, 12:11 AM
  4. Fwd: sorting library of congress numbers
    By Mike Rylander in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 20th, 12:08 AM
  5. Sorting With a UNION Query?
    By MDW in forum ASP Database
    Replies: 2
    Last Post: June 10th, 03:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139