Search code examples
sqlstringsql-order-by

T-SQL ORDER BY number and letters mixed in string

I have a range of unique strings which contain numbers and sometimes numbers and a letter, a sample of which reads:

  • 1X
  • 2X
  • 2Y
  • 12X
  • 20
  • 21

The number/s always precede the letter. What is the ORDER BY (T-SQL) clause solution to produce a list which would give me the order as demonstrated above?

I tried using

LEN(fieldName), fieldname - which would work but for the 20 and 21. I have tried expressing the strings as an integer but the CAST fails in the conversion process.

Solution

  • I'm stealing my details from Here.

    declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
 select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'

select 
    left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr 
   ,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t

    which results in

    122345684               XT
23339034300             -XT
422222222111111111232   
423432424523242332      X
423842389034209         XYZ
ABC

    To use it in your context.

    SELECT * 
FROM YourTable 
ORDER BY left(s,patindex('%[^0-9]%',S+' ')-1 ), 
         right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)

    Shown by

    declare @t table(s varchar(25))
insert @t
select '12X' union
select '1X' union
select '2X' union
select '2Y' union
select '20' union
select '21'

SELECT * 
FROM @t
ORDER BY CAST(left(s,patindex('%[^0-9]%',S+' ')-1 ) AS INT), 
         right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)

    Results In

    1X
2X
2Y
12X
20
21