CODEWITHSUNDEEP
sqlsql-serverstringcolumnsorting
Eric Brochu
Eric Brochu

Reputation: 109

SQL alphanumerical sorting with non specific format

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?

Upvotes: 0

Views: 96

Answers (3)

Eric Brochu
Eric Brochu

Reputation: 109

Thx guys for all your infos. I manage to get the thing works almost perfectly with this particular case.

select distinct PCE_NAM,
    --FIRST SECTION OF THE STRING
    --REPLICATE to fill 0s before numerics
    (CASE 
        WHEN CHARINDEX('-', PCE_NAM) = 0 then PCE_NAM
        WHEN CHARINDEX('-', PCE_NAM) > 0 and 
            ISNUMERIC(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)) = 1 and
            LEN(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)) <= 8
            then REPLICATE('0', 8-LEN(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1))) + LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)
        ELSE LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)
    END) as FirstPart,
    --SECOND SECTION OF THE STRING
    (CASE 
        WHEN CHARINDEX('-', PCE_NAM) = 0 then ''
        WHEN CHARINDEX('-', PCE_NAM) > 0 and 
            ISNUMERIC(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))) = 1 and
            LEN(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))) <= 8
            then REPLICATE('0', 8-LEN(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)))) + SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))
        ELSE SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))
    END) as SecondPart,
    --GET THE NUMERICS ONLY FROM THE SECOND SECTION SO CAN SORT PROPERLY
    (CASE 
        WHEN LEN(LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))) <= 8
            then REPLICATE('0', 8-LEN(
            LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)))))) + 
            LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))
        ELSE LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))
    END) as SecondPartF
from PARTS
order by FirstPart, SecondPartF, SecondPart

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 3

bjorsig
bjorsig

Reputation: 1107

You could split the string into number and text (on the '-') and sort then by the num column. Something like:

select iif((charindex('-', str) > 0), (cast(substring(str, 1, (charindex('-', str)-1)) as integer)), 9999) as num, str
from (
    select '1-ABC' as str union all
    select '2-ABC' as str  union all
    select '12-ABC' as str  union all
    select 'ABC' as str
) tbl
order by num, str

This way you will sort by the numerical value first and then by the string. Here I just add a really high number to the none number values so the it will be ordered last.

Upvotes: 0

Related Questions