Search code examples
mysqlsqldatabaserdbms

Mysql Order by with string and integer combination

I have a column in mysql table contains different types of value...

A-1

A-2

B-7

AA-1

B-1

C-2

Only Common thing in all row is "-".

However i want to rearrange all values is like this...

A-1

A-2

AA-1

B-1

B-7

C-2

I tried several order by process like

ORDER BY col+0

ORDER BY cast(col as unsigned)

ORDER BY length(col),col

ORDER BY CAST(col AS SIGNED) ASC

ORDER BY ABS(col)

None of them are work as expected. Any ideas?

Solution

  • This will work:

    ORDER BY SUBSTRING_INDEX(col, '-', 1), 
    CAST(SUBSTRING_INDEX(col, '-', -1) AS UNSIGNED)

    Read more about this function here: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

    However, it will be bad for performance, because it has to generate those substrings for every row, and then sort them manually.

    If you use MySQL 5.7 or later, you can create virtual columns and an index for them:

    ALTER TABLE MyTable
  ADD COLUMN col_field1 VARCHAR(2), AS (SUBSTRING_INDEX(col, '-', 1)),
  ADD COLUMN col_field2 INT UNSIGNED AS (SUBSTRING_INDEX(col, '-', -1)),
  ADD INDEX (col_field1, col_field2);

    Then you can sort by the indexed virtual columns:

    ORDER BY col_field1, col_field2