SQL Sort verions numeric + string

Question

I have to sort a table by the version column which is a random string + numeric(x.xx.xx).For example my table looks like:

string 1.14.2
string 1.14.1
string 1.14
string 1.14.3
string 1.14.4
string 1.11
string 1.10
string 1.10.2
string 1.9
string 1.9.4
string 1.9.2
string 1.8
string 1.8.8
string 1.4.6

and my result has to look like:

string 1.14.4
string 1.14.3
string 1.14.2
string 1.14.1
string 1.11
string 1.10.2
string 1.10
string 1.9.4
string 1.9.2
string 1.9
string 1.8.8
string 1.8
string 1.4.6

my current sql is

SELECT * FROM t_plugins WHERE a_category = 'string' AND a_master_ig = 2
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(trim(a_name), " ", -1), ".", -1)```

GMB · Accepted Answer · 2019-11-21 00:51:09Z

Something like this should work:

order by
    cast(
        substring_index(
            substring_index(a_name, ' ', -1),
            '.', 
            1
         ) 
         as unsigned
    ) desc,
    cast(
        right(
            substring_index(a_name, ' ', -1),
            length(substring_index(a_name, ' ', -1)) 
                - locate('.', substring_index(a_name, ' ', -1))
        )
        as decimal(10, 5)
    ) desc

The idea is to :

separate the string from the version number
isolate the first digit of the version number (and cast it to an integer value) and use it as the first sort criteria
then isolate the second and third part, cast that to a decimal value, and use it as second sort criteria

Demo on DB Fiddle:

| a_name        |
| :------------ |
| string 1.14.4 |
| string 1.14.3 |
| string 1.14.2 |
| string 1.14.1 |
| string 1.14   |
| string 1.11   |
| string 1.10.2 |
| string 1.10   |
| string 1.9.4  |
| string 1.9.2  |
| string 1.9    |
| string 1.8.8  |
| string 1.8    |
| string 1.4.6  |

Gordon Linoff · Accepted Answer · 2019-11-21 01:06:56Z

0

I think this is simpler using implicit conversion:

order by substring_index(a_name, '.', 1) + 0,
         substring_index(substring_index(a.name, '.', 2), '.', -1) + 0,
         substring_index(a_name, '.', -1) + 0

answered Nov 21, 2019 at 1:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Jose Bagatelli · Accepted Answer · 2019-11-21 01:08:35Z

0

Assuming there's no space within your "random string", this should give you the result you expect.

select 
    *
from 
    your_table
order by 
    cast('/' + TRIM(SUBSTRING(your_version_col,CHARINDEX(' ', your_version_col),LEN(your_version_col)))  + '/' as hierarchyid) desc

answered Nov 21, 2019 at 1:08

Jose Bagatelli

1,4111 gold badge20 silver badges35 bronze badges

Collectives™ on Stack Overflow

SQL Sort verions numeric + string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related