MySQL order by string with numbers

mysql sql-order-by collation
Adam Esterle picture Adam Esterle · Aug 23, 2012 · Viewed 20.4k times · Source

I have strings such as M1 M3 M4 M14 M30 M40 etc (really any int 2-3 digits after a letter) When I do " ORDER BY name " this returns:

M1, M14, M3, M30, M4, M40

When I want:

M1, M3, M4, M14, M30, M40 Its treating the whole thing as a string but I want to treat it as string + int

Any ideas?

Answer

rocky3000 picture rocky3000 · Aug 23, 2012

You could use SUBSTR and CAST AS UNSIGNED/SIGNED within ORDER BY:

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More