SQL alphanumerical sorting with non specific format

Question

I got to sort strings that do not share the same format and I got trouble finding a solution for this one. Tried several options on stackoverflow for this but it does not work for me because they are for specific formatted data.

Here are an example of data i must sort.

12-ABC
1-ABC
ABC-10
ABC-11
ABC-100
2-ABCD
ABC-100A

I got results like this with ORDER BY

1-ABC
12-ABC
2-ABCD
ABC-10
ABC-100
ABC-100A
ABC-11

But i would like to have this

1-ABC
2-ABCD
12-ABC
ABC-10
ABC-11
ABC-100
ABC-100A

How would you do it?

Answer 1

This is a complicated problem. Parsing strings is something that SQL is not designed for in general, and SQL Server in particular.

You are trying to extract a number from two parts of a string and sort by that number. Arrgg! That really suggests that you are storing multiple types of information in a string -- things that would perhaps be better represented in separate columns.

That said, you can do what you want. Here is one method that works on the data you supplied in the question:

select t.str
from (values ('1-ABC'),
             ('12-ABC'),
             ('2-ABCD'),
             ('ABC-10'),
             ('ABC-100'),
             ('ABC-100A'),
             ('ABC-11')
     ) t(str) cross apply
     (values (left(str, charindex('-', str + '-') - 1), stuff(str, 1, charindex('-', str), ''))
     ) v(part1, part2)
order by coalesce(try_convert(int, v.part1), 999999999),
         part1,
         try_convert(int, left(v.part2, patindex('%[^0-9]%', v.part2 + 'x') - 1)),
         part2;

Here is a db<>fiddle.