CODEWITHSUNDEEP
mysqlsqldatabaserdbms
Mahbubur Rahman Khan
Mahbubur Rahman Khan

Reputation: 415

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?

Upvotes: 2

Views: 734

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

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

Upvotes: 1

Related Questions