Ordering issue concerning numbers and letters in MYSQL SELECT statement

Question

I have a table with a column 'tag' that contains numbers and letters, such as:

When I run the following query, I don't get the desired order:

SELECT * FROM `items` ORDER BY `tag` ASC;

This would output as (1, 11, 12, 2, 20, 4a, 4b, P1)

I then found a somewhat good workaround by using either "+ 0" or "* 1" in my query:

SELECT * FROM `items` ORDER BY `tag` + 0 ASC;

This would output as (Z1, 1, 2, 3, 4b, 4a, 10, 11, 12, 20), which is only slightly better

Is there any way to ORDER the values so they appear as:

(1, 2, 3, 4a, 4b, 10, 11, 12, 20, Z1)

I would like to avoid adding an order column, because I have 30,000 items and the tags are only subsets of larger categories.

May be help you http://stackoverflow.com/questions/5417381/mysql-sort-string-number — Lal krishnan S L, Feb 20 '14 at 10:06
SQLFiddle appears to be down so no means of converting my sql-server solution... I think you need to ORDER BY: letters from left of string ('0' where none present), numbers (from left or right), letters from right ('0' where none present). But I think you might want to rethink whether this is a good field to sort by. — OGHaza, Feb 20 '14 at 11:54
Thanks guys, I've tried everything I could find and have had no good results. I am just going to add an order column and take a few days to sort the data manually (to some point). — flip66, Feb 23 '14 at 17:17

Mani · Answer 1 · 2014-02-20T11:57:52.340

0

try this one:

SELECT * FROM `items` ORDER BY  CAST(`tag` as UNSIGNED) ASC;

EDIT:

SELECT * FROM items
ORDER BY (tag REGEXP '[0-9]^'),
         CASE WHEN tag REGEXP '^[0-9]' THEN tag*1 ELSE tag END;

edited Feb 20 '14 at 11:57

answered Feb 20 '14 at 10:10

Mani

888
6
19

Hi, this outputs (Z1, 1, 2, 3, 4a, 4b, etc) – flip66 Feb 20 '14 at 10:45

valex · Answer 2 · 2014-02-21T11:46:07.167

0

It seems that you want to separate strings started with a digit and started with a letter. If so then try this:

SELECT tag, tag*1,(tag REGEXP '^[0-9]') FROM items
ORDER BY (tag REGEXP '^[0-9]') DESC,
         tag*1,tag

SQLFiddle demo

edited Feb 21 '14 at 11:46

answered Feb 20 '14 at 10:56

valex

23,966
7
43
60

Hi, this outputs (Z1, 1, 10, 11, 12, 2, 20, etc..) – flip66 Feb 20 '14 at 11:10
@user1024370 I've changed the query. Try it now – valex Feb 21 '14 at 11:46
This is clever and works for the test data. However, it will fail if you want Z1, Z2, and Z10 to also sort naturally. – toxalot Feb 24 '14 at 02:18

score 0 · Answer 3 · edited May 23 '17 at 10:31

Update: This appears to be very non-trivial to achieve in MySQL. Even if you did implement it I imagine it would be cripplingly slow. If you must order by this tag I suggest breaking it up into several columns and having the tag column as the computed concatenation of those columns

Your tags are in the format: optional set of letters, an integer, another optional set of letters. To order these correctly you're going to extract each part of the tag, so that you can compare the letters as string, and the digits as integers.

SQLFiddle is down so the following is a SQLServer solution to the problem, it might help you (or someone else who can help) visualise how to do this in MySQL though the query will be very different. In the following a is the tag.

SELECT a
   ,CASE WHEN a LIKE '[^0-9]%' 
         THEN LEFT(a, ISNULL(NULLIF(PATINDEX('%[^0-9][0-9]%', a ),0),LEN(a)))
         ELSE '' 
    END AS StartLetter   
   ,CAST(SUBSTRING(a 
                   ,PATINDEX('%[0-9]%', a )
                   ,(LEN(a)+2-PATINDEX('%[0-9]%', REVERSE(a)))-PATINDEX('%[0-9]%', a))
         AS INT) AS Number
   ,SUBSTRING(a
             ,LEN(a)+2-PATINDEX('%[0-9]%', REVERSE(a) )
             ,LEN(a)-1) AS EndLetter
FROM @t
ORDER BY STartLetter, Number, EndLetter

Which produces the following (for your data + some additional test data):

+-----+-------------+--------+-----------+
|  a  | StartLetter | Number | EndLetter |
+-----+-------------+--------+-----------+
| 1   |             |      1 |           |
| 2   |             |      2 |           |
| 3   |             |      3 |           |
| 4ab |             |      4 | ab        |
| 4b  |             |      4 | b         |
| 10  |             |     10 |           |
| 11  |             |     11 |           |
| 12  |             |     12 |           |
| 20  |             |     20 |           |
| B1  | B           |      1 |           |
| B22 | B           |     22 |           |
| BA2 | BA          |      2 |           |
| Z1  | Z           |      1 |           |
| Z2b | Z           |      2 | b         |
| Z3  | Z           |      3 |           |
+-----+-------------+--------+-----------+

Still, given how complex that is just to order the data, I think you need to rethink your decision to order by the column in the first place.

Ordering issue concerning numbers and letters in MYSQL SELECT statement

3 Answers3