# 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. ## 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. ## 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

--
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. ## 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. ## 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. ## 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",

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

#### Posting Permissions

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