Smithy Smithy - 3 months ago 9
SQL Question

SQL Ordering of a string

I have a column of strings which i am trying to get in the order currently shown below.

I have tried substrings, case statements and using the Right syntax to try and achieve the proper ascending order but i cannot figure it out.

The string will never exceed a length of 4, e.g A10a is as large as it can get and it always follows this format (string, number, string)

A1,
A2,
A2a,
A2b,
...,
...,
...,
A10,
A10a


I was trying to perhaps extract the last character and if its a letter then convert it to a number using a case statement, anybody know if this would work?

Is it possible? Any help here appreciated

Answer

This is really ugly, but works:

declare @test table (test varchar(4))


insert into @test values ('A1')
insert into @test values ('A2')
insert into @test values ('A2a')
insert into @test values ('A2b')
insert into @test values ('A10')
insert into @test values ('A10a')
insert into @test values ('A10b')

select * ,
 LEFT(test,1), 
 CAST(CASE 
 WHEN LEN(test) =3 AND right(test,1) like '[a-z]' THEN substring(test,2,1)
ELSE substring(test,2,2)
END AS INTEGER)
,CASE WHEN right(test,1) like '[a-z]' THEN RIGHT(test,1) ELSE '' END    

from @test
ORDER BY 
 LEFT(test,1), 
 CAST(CASE 
 WHEN LEN(test) =3 AND right(test,1) like '[a-z]' THEN substring(test,2,1)
ELSE substring(test,2,2)
END AS INTEGER)
,CASE WHEN right(test,1) like '[a-z]' THEN RIGHT(test,1) ELSE '' END