Skip to main content
N
Newsgroup_User
Inspiring
December 13, 2006
Question

Ordering mixed numeric / alphanumeric list

  • December 13, 2006
  • 3 replies
  • 4846 views
I have a list of "lotnumbers" in a varchar mySQL field
Data is mixed 103, 11, A3, G5, etc

Before the Lettered entries got dropped in the mix,
I was using "ORDER by CAST(lotNumber as SIGNED) "
which put the numeric entries into numeric order (1, 2, 3, etc)
but now that I have the letters in there, this is understandably
screwy... the mixed entries seem to be in no particular order
(D8, B8, C1, etc ) then the numeric entries below those in perfect order as before

the SQL "Order by LotNumber"
gives me the usual 1, 10, 100, 101, 102, etc, with the alpha-combo entries at the end, in order A1, A2, A3, etc... this is as
expected and I have left it in place for now

What is the correct "order by" syntax to get all of the numeric entries listed numerically, with the mixed alphanumeric entries
either before or after those (doesn't matter), in regular alphabetical order?

TIA

--

Michael Evangelista
Evangelista Design
www.mredesign.com
--




    This topic has been closed for replies.

    3 replies

    N
    Newsgroup_UserAuthor
    Inspiring
    December 14, 2006
    Hi Phil -
    thanks for the reply.
    I did a bit of googling on 'mysql isnumeric()'
    and the best suggestion seemed to be to use a
    regular expression, i.e.
    SELECT * FROM myTable WHERE myField REGEXP ('[0-9]')
    or something like
    =====
    SELECT fieldName*1=0 as IsNumeric;
    You'll get a 0 for any text, a 1 for any numbers (except if fieldName contains 0); you could watch for that by getting more
    complicated:
    SELECT if(fieldName = 0, 1, fieldName*1=0) as IsNumeric;
    ======

    This seems somewhat similar to what you initially proposed,
    but I am still not sure how to write that into my original query...
    any suggestions?


    --

    Michael Evangelista
    Evangelista Design
    www.mredesign.com
    --



    "paross1" <webforumsuser@macromedia.com> wrote in message news:elppi9$qt1$1@forums.macromedia.com...
    > With SQL Server, you can do something like this.....
    >
    > SELECT LotNumber,
    > CASE WHEN ISNUMERIC(LotNumber) = 1
    > THEN CAST(LotNumber AS int)
    > ELSE 0 END AS sortval
    > FROM your_table
    > ORDER BY 2, 1
    >
    > But, I can't find a function in MySQL that is equivalent to ISNUMERIC() in SQL
    > Server.....
    >
    > Phil
    >


    P
    paross1
    Participating Frequently
    December 14, 2006
    Hmmmmm.... I'm not a MySQL user, so this is a total SWAG. Something like this may allow you to sort your entries with alpha characters first, then the numerics. In other words, if LotNumber contains an alpha (LotNumber*1 = 0) or is not the number 0, then the second column in your query is a 0, otherwise LotNumbr is cast as a number. SInce you are sorting by the 2nd column first, then the forst column, als columns with alphas wil be sorted in alpha order (since the sortval column will be 0, and you sort on that column first), then you will sort on the numeric fields numerically.... at least I think that is how it will work.

    SELECT LotNumber,
    CASE WHEN (LotNumber*1 = 0 AND LotNumber <> 0)
    THEN 0
    ELSE CAST(LotNumber AS signed) END AS sortval
    FROM your_table
    ORDER BY 2, 1;

    Phil
    P
    paross1
    Participating Frequently
    December 13, 2006
    With SQL Server, you can do something like this.....

    SELECT LotNumber,
    CASE WHEN ISNUMERIC(LotNumber) = 1
    THEN CAST(LotNumber AS int)
    ELSE 0 END AS sortval
    FROM your_table
    ORDER BY 2, 1

    But, I can't find a function in MySQL that is equivalent to ISNUMERIC() in SQL Server.....

    Phil
    D
    Dan_Bracuk
    Inspiring
    December 13, 2006
    The way you did it seems to do what you want, as long as you want the alphas after the numbers.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices