Professional Web Applications Themes

How to order mixed letter-number strings? - MySQL

Hello, maybe someone can help me with this: I have a varchar column containing entries that I want to sort. Each entry consists of a combination of letters, and an optional number. Example: aaa aaa 2 aaa 4 aaa 12 bbb bbb 4 ccc 1 ccc 2 ddd eee Obviously I want these entries ordered in the human way: alphabetically AND numerically, as above, where a-with-2 comes before a-with-12 (since the integer 12 is larger than the integer 2). If I use ORDER BY thiscolumn, I get this result: aaa aaa 12 aaa 2 aaa 4 etc. that is, these ...

  1. #1

    Default How to order mixed letter-number strings?

    Hello, maybe someone can help me with this:

    I have a varchar column containing entries that I want to sort. Each
    entry consists of a combination of letters, and an optional number.

    Example:

    aaa
    aaa 2
    aaa 4
    aaa 12
    bbb
    bbb 4
    ccc 1
    ccc 2
    ddd
    eee

    Obviously I want these entries ordered in the human way:
    alphabetically AND numerically, as above, where a-with-2 comes before
    a-with-12 (since the integer 12 is larger than the integer 2).

    If I use ORDER BY thiscolumn, I get this result:

    aaa
    aaa 12
    aaa 2
    aaa 4
    etc.

    that is, these entries are ordered strings, alphabetically, with a-
    with-12 coming before a-with-2, because the character 1 comes before
    the character 2, regardless of the next character (just as aardvark
    comes before abba in a list).

    I am working with mySQL 3.23.56 and can't update (shared hosting).

    How do I order my mixed column alphabetically AND numerically?

    Ciuin Guest

  2. #2

    Default Re: How to order mixed letter-number strings?

    On 25 Feb 2007 12:17:27 -0800, Ciuin wrote: 

    Can't. All you can do is make whatever puts the numbers in there pad the
    leading edge with spaces.

    --
    3. My noble half-brother whose throne I usurped will be killed, not kept
    anonymously imprisoned in a forgotten cell of my dungeon.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: How to order mixed letter-number strings?

    >I have a varchar column containing entries that I want to sort. Each 

    Your examples violate this, in that they seem to contain spaces, which
    are neither letters nor numbers.
     

    Assume this value is kept in the field `str`.

    You can ORDER BY an expression, or several of them. Your examples
    don't give the full generality of the data, but let's try to do it anyway:

    left(str, 4) is the alpha part (assuming it's always 4 characters long
    including the space)
    substr(str, 5) is the numeric part as a string.
    (int)substr(str, 5) is the numeric part as a number.

    so try:

    ORDER BY left(str,4), (int)substr(str,5)
    (order by the alpha part, then break ties with the numeric part,
    treated as a number)

    This does NOT handle the case of no numeric part. It also assumes that
    the alpha part is fixed-length. If you can assume there's a space in there,
    you can use locate() to find the position of the space. Stuff like if() or
    ifnull() could handle the case where the numeric part comes out a zero-length
    string or NULL.

    I think you can handle the no-numeric-part issue with
    if(substr(str,5) = '', '0', substr(str, 5)) as the numeric part as
    a string, defaulting to '0' if it isn't there.
    (int)if(substr(str, 5) = '', '0', substr(str, 5)) is the numeric
    part as a number.

    Gordon Guest

  4. #4

    Default Re: How to order mixed letter-number strings?

    Thank you Gordon for your suggestions. I'll try them out and post the
    result later.

    About the space in my example:
     
    >
    > Your examples violate this, in that they seem to contain spaces, which
    > are neither letters nor numbers.[/ref]

    I just put the spaces in my example to make it more readable. I am
    sorry, if this has led to confusion. The real strings look like this:

    dsf-mnsb/omlkds-lkdsfg/12
    mnvvc/oizz-ljsdflkjs
    etc.

    i.e. the "alphabetic" part differs in length and may contain minuses
    or slashes. But, as you suggested for the space:
     

    I might actually find the position of the second slash or, maybe
    ereg('/[0-9]+$', $string), if that works.

    I'll try it and post the result.

    Ciuin Guest

  5. #5

    Default Re: How to order mixed letter-number strings?

    Gordon, I tried your suggestions, here is my result.

    I give a detailed explanation of the final query string (see "Complete
    query string" below) in the hope that this may help someone with a
    similar problem understand what I did:


    The problem
    -----------

    I have strings of the type

    aaa/mmm
    aaa/mmm1
    aaa/mmm2
    aaa/mmm12
    bbb/nnn
    ccc/ooo/4
    ccc/ooo/7

    I want to sort these strings alphabetically (so that aaa comes before
    bbb) and numerically (so that 2 comes before 12). The result should be
    the order above. If I simply use "ORDER BY string", then 12 comes
    before 2, because the letter 1 comes before the letter 2, regardless
    of the following letters, just as "mmab" comes before "mmb".


    Splitting the strings:
    ----------------------

    Since my strings all have one slash in the middle and one slash before
    the optional number (e.g. vbnm/oeqs/2), I need the location of the
    second slash to split the number from the "alphabetical" part of the
    string.

    The first slash can be found with
    LOCATE("/", string)

    e.g.
    LOCATE("/", "vbnm/oeqs/2")
    returns 5

    We can then find the second slash from this position with
    LOCATE("/", string, LOCATE("/", string) + 1).

    Here the first LOCATE function acts as the third (optional) parameter
    in the outer LOCATE function, which gives the starting position for
    the second search as one position behind the first found slash. If we
    don't add "+ 1", then the outer LOCATE starts at the position of the
    first slash and, since it includes the starting position, returns the
    first slash again.

    e.g.

    LOCATE("/", "vbnm/oeqs/2")
    returns 5 [first /]

    LOCATE("/", "vbnm/oeqs/2") + 1
    returns 6 [o]

    LOCATE("/", "vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/2"))
    starts at 5 [first /] and returns 5 [first /]

    LOCATE("/", "vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/2") + 1)
    starts at 6 [o] and returns 10 [second /]


    Extracting the "alphabetic" part
    --------------------------------

    We can extract the "alphabetical" part from the string with
    LEFT(string, pos)
    where pos is the position of the second slash. Complete with the
    formula from above this is
    LEFT(string, LOCATE("/", string, LOCATE("/", string) + 1) - 1)

    Here I have added "- 1", to strip the second slash from the
    "alphabetical" part, so that the resulting string is exactly like it
    would be, if it had no number.

    e.g.

    LEFT("vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/
    2") + 1))
    returns "vbnm/oeqs/"

    LEFT("vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/2", LOCATE("/", "vbnm/oeqs/
    2") + 1) - 1)
    returns "vbnm/oeqs"

    But since some of the strings do not end in the optional slash plus
    number, we need to differentiate: Trying to find the second slash, if
    there is none, will result in NULL, and if we try to split the string
    there, we will get NULL again. So we use IF thus:

    IF(a, b, c)

    which reads: "if a is true, return b, else return c". Complete with
    the formula from above this is (written in three lines)

    IF(
    LOCATE("/", string, LOCATE("/", string) + 1) = "",
    string,
    LEFT(string, LOCATE("/", string, LOCATE("/", string) + 1) - 1)
    )

    which reads: "if splitting the string at the second slash returns
    nothing (because there is no second slash), simply take the complete
    string, otherwise split the string at the second slash"

    for "vbnm/oeqs/2" and "vbnm/oeqs" this will return "vbnm/oeqs".


    Extracting the numeric part
    ---------------------------

    We extract the numeric part (after the second slash) with
    SUBSTRING(string, pos)
    where pos is the position of the second slash.

    Again we have to take care of the strings without second slash and
    number by IF(a, b, c), so we get

    IF(
    SUBSTRING(string, LOCATE("/", string, LOCATE("/", string) + 1) + 1) =
    "",
    "0",
    SUBSTRING(string, LOCATE("/", string, LOCATE("/", string) + 1) + 1)
    )

    The second "+ 1" here gives us the position behind the second slash as
    the beginning point for SUBSTRING. Otherwise we would get e.g. "/23",
    instead of "23".


    Type conversion
    ---------------

    Now we have split the numbers from the strings, but to sort in a
    numerical order, we need to convert the strings that we have
    (consisting of the letters 0 to 9) to integers.

    (int)string as given in Gordon's example is from PHP and does not work
    in mySQL, but instead you can use
    CAST(string AS UNSIGNED INTEGER).


    Complete query string
    ---------------------

    So the final query string to order a mixed alphanumerical string that
    begins with letters and ends with numbers and is structured by slashes
    is (with line breaks for better readability):

    SELECT string FROM table ORDER BY

    IF(LOCATE("/", string, LOCATE("/", string) + 1) = "",
    string,
    LEFT(string, LOCATE("/", string, LOCATE("/", string) + 1) - 1)),

    CAST(
    IF(
    SUBSTRING(string, LOCATE("/", string, LOCATE("/", string) + 1) + 1) =
    "",
    "0",
    SUBSTRING(string, LOCATE("/", string, LOCATE("/", string) + 1) + 1))
    AS UNSIGNED INTEGER)


    You can add ASC or DESC, if necessary.

    Thank you Gordon for your inspiring comment!

    Ciuin Guest

Similar Threads

  1. Replies: 11
    Last Post: October 9th, 07:30 PM
  2. Changing from half letter to letter doent size
    By Jane_Evans@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 8
    Last Post: August 22nd, 05:56 PM
  3. How do I convert strings to floating number?
    By Marcus Claesson in forum PERL Beginners
    Replies: 6
    Last Post: October 14th, 03:02 PM
  4. SQL: is it possible to ORDER BY just the column number?
    By Dragonhunter in forum ASP Database
    Replies: 9
    Last Post: September 23rd, 08:02 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