SQL Order Alphanumeric String of fixed length first by alpha-chars and then by numeric-values-CodePudding

How do I order the following data by alpha characters before ordering by numeric values in SQL Server?

'00141W703','001413541','00141B204','00141A586','00900W738','0075W0825','001C5A389'

Expected Results

'001C5A389'
'00141A586'
'00141B204'
'00141W703'
'001413541'
'0075W0825'
'00900W738'

All the similar questions asked here previously are for specific string patterns wherein the data to be sorted/ordered are either starting with alpha-chars/numeric-values. But in this case the string to be ordered are of fixed length but the position of alpha-chars and numeric-values are completely random. It's almost as if they sort each character from left to right with alpha-chars ordered first ascending and then followed by numeric-values in the ascending order.

A sample data set given below:

CREATE TABLE tab
    (col varchar(9));
    
INSERT INTO tab
    (col)
VALUES
    ('00141W703'),
    ('001413541'),
    ('00141B204'),
    ('00141A586'),
    ('00900W738'),
    ('0075W0825'),
    ('001C5A389');

I appreciate the solutions provided are tested on fiddle - https://dbfiddle.uk/qHEWHO4F

CodePudding user response：

This is a very crude, brute force method, there is bound to be a better solution out there.

Essentially you sort by each column, first if its an alpha character, then a second time if its a numeric character.

Using "z" and "9" to ensure nulls come last.

select *
from tab
order by
    case when isnumeric(substring(col,1,1)) = 0 then substring(col,1,1) else 'z' end asc,
    case when isnumeric(substring(col,1,1)) = 1 then substring(col,1,1) else '9' end asc,
    case when isnumeric(substring(col,2,1)) = 0 then substring(col,2,1) else 'z' end asc,
    case when isnumeric(substring(col,2,1)) = 1 then substring(col,2,1) else '9' end asc,
    case when isnumeric(substring(col,3,1)) = 0 then substring(col,3,1) else 'z' end asc,
    case when isnumeric(substring(col,3,1)) = 1 then substring(col,3,1) else '9' end asc,
    case when isnumeric(substring(col,4,1)) = 0 then substring(col,4,1) else 'z' end asc,
    case when isnumeric(substring(col,4,1)) = 1 then substring(col,4,1) else '9' end asc,
    case when isnumeric(substring(col,5,1)) = 0 then substring(col,5,1) else 'z' end asc,
    case when isnumeric(substring(col,5,1)) = 1 then substring(col,5,1) else '9' end asc,
    case when isnumeric(substring(col,6,1)) = 0 then substring(col,6,1) else 'z' end asc,
    case when isnumeric(substring(col,6,1)) = 1 then substring(col,6,1) else '9' end asc,
    case when isnumeric(substring(col,7,1)) = 0 then substring(col,7,1) else 'z' end asc,
    case when isnumeric(substring(col,7,1)) = 1 then substring(col,7,1) else '9' end asc,
    case when isnumeric(substring(col,8,1)) = 0 then substring(col,8,1) else 'z' end asc,
    case when isnumeric(substring(col,8,1)) = 1 then substring(col,8,1) else '9' end asc,
    case when isnumeric(substring(col,9,1)) = 0 then substring(col,9,1) else 'z' end asc,
    case when isnumeric(substring(col,9,1)) = 1 then substring(col,9,1) else '9' end asc;