Order column by numbers containing . and then alphabetically

Question

I want to order one of my tables column named smsfilename which contains values like this:

id	smsfilename
1	1.1.18 Termino
2	2 abcc
3	1.1.1 xyz
4	1.1.5 ship
5	1.1.11 abc
6	1.1.11 aa
7	1.2 aarun
8	1.1 arun
9	1.1.10 vesta
10	1.1.1 TrojanWar

I want to sort the values like this:

id	smsfilename
1	1.1 arun
2	1.1.1 TrojanWar
3	1.1.1 xyz
4	1.1.5 ship
5	1.1.10 vesta
6	1.1.11 aa
7	1.1.11 abc
8	1.1.18 Termino
9	1.2 aarun
10	2 abcc

I have used this mysql query, but it does not achieve my expected result

select * 
from smsproceduresfiles 
order by cast('/' + replace(smsFileName , '.', '/') + '/' as UNSIGNED) ASC;

Tushar · Answer 1 · 2023-03-18T09:46:21.140

If want to sort the values in smsfilename column first in numeric and then alphabetically; then you do it as below :

SELECT *
FROM smsproceduresfiles
ORDER BY 
  SUBSTRING_INDEX(smsFileName, ' ', 1) + 0,
  LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(smsFileName, ' ', 1), '.', -1), 10, '0'),
  smsFileName;

Here is a working SQLFiddle

You will be also able to do it using INET_ATON function in mysql as follows:

SELECT *
FROM smsproceduresfiles
ORDER BY 
  INET_ATON(
    CONCAT(
      SUBSTRING_INDEX(smsFileName, ' ', 1), 
      '.', 
      LPAD(
        SUBSTRING_INDEX(SUBSTRING_INDEX(smsFileName, ' ', 1), '.', -1), 
        10, 
        '0'
      )
    )
  ),
  smsFileName;

Here is a working Demo

Now, If you want to keep the id column also sorted (from 1 to 10) as in expected output; You can do it using ROW_NUMBER() in MYSQL 8.0.

SELECT ROW_NUMBER() OVER () AS id, smsfilename
FROM smsproceduresfiles
ORDER BY 
  SUBSTRING_INDEX(smsFileName, ' ', 1) + 0,
  LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(smsFileName, ' ', 1), '.', -1), 10, '0'),
  smsFileName;

Here is the working DBFIDDLE

This gets you the expected output as :

id	smsfilename
1	1.1 arun
2	1.1.1 TrojanWar
3	1.1.1 xyz
4	1.1.5 ship
5	1.1.10 vesta
6	1.1.11 aa
7	1.1.11 abc
8	1.1.18 Termino
9	1.2 aarun
10	2 abcc

Order column by numbers containing . and then alphabetically

1 Answers1