Search code examples
sqlsql-serversql-order-bypatindex

Sorting an alphanumeric column in SQL with mix of alphanumeric and alpha-only values


I have a requirement where I have to apply sort in an SQL query by an Alphanumeric column such that the values in it get sorted like the following:

AD
AH
AK1
B1
B2
B3x
B3y
B11
C1
C6
C12x
UIP

instead of the default way:

AD
AH
AK1
B1
B11
B2
B3x
B3y
C1
C12x
C6
UIP

I was able to write the following SQL statement:

SELECT [MyColumn]
FROM [MyTable]
ORDER BY
    LEFT([MyColumn], PATINDEX('%[0-9]%', [MyColumn]) - 1),
    CONVERT(INT, SUBSTRING([MyColumn], PATINDEX('%[0-9]%', [MyColumn]), LEN([MyColumn]) - (PATINDEX('%[0-9]%', REVERSE([MyColumn]))))),
    RIGHT([MyColumn], PATINDEX('%[0-9]%', [MyColumn]) - 1)

I just need to know how to implement this in a conditional way such that this doesn't get triggered for alpha-only value else it would give error, or if there is a way to make it work with that as well?

P.S. The environment is SQL Server 2014.


Solution

  • It is a bit of a mess to look at and I agree with Sean Lange that you should look into recording this in more than one field, but you can use case expressions within your order by to have conditional ordering:

    declare @t table(c nvarchar(10));
    insert into @t values('AD'),('AH'),('AK1'),('B1'),('B2'),('B3x'),('B3y'),('B11'),('C1'),('C6'),('C12x'),('UIP');
    
    select c
    from @t
    order by
          case when patindex('%[0-9]%', c) = 0
               then c
               else left(c, patindex('%[0-9]%', c) - 1)
          end
          ,case when patindex('%[0-9]%', c) = 0
               then 0
               else convert(int,substring(c,patindex('%[0-9]%', c),len(c) - (patindex('%[0-9]%', reverse(c)))))
          end
          ,right(c,case when patindex('%[0-9]%', c) = 0
                       then 0
                       else patindex('%[0-9]%', c) - 1
                   end
          );
    

    Output:

    +------+
    |  c   |
    +------+
    | AD   |
    | AH   |
    | AK1  |
    | B1   |
    | B2   |
    | B3x  |
    | B3y  |
    | B11  |
    | C1   |
    | C6   |
    | C12x |
    | UIP  |
    +------+