Sort alphanumeric values in SQL Server

whatever you like, but i would leave them just as is

did you at least try it? how did it work? (hint: it worked fine)

To make it more flexibel you can use this

select x from t order by cast(substr(x, 1 ,5) as unsigned), x

Here is the complete fiddle

https://www.db-fiddle.com/f/9F1BndGCQUyyZJyRg5yf8w/1

The substr is only nessesary if you have numbers in the String behind the numbers to sort for. You can change the 5 to 3 if the maximum number is 999 for exmple

sorry, no

here are the values that you provided in your fiddle –

insert into t values 
('17b'),('17a'),('17ba'),('2bb'),
('3a'),('3'),('2a'),('2ab'),('2b'),('2ba');

here are the results produced by your query –

select x from t order by cast(x as unsigned), x

x
---
2a
2ab
2b
2ba
2bb
3
3a
17a
17b
17ba

however, if you look back in post #1, you can see that 2ab is supposed to come after 3

so your solution doesn’t work

neither does order by cast(substr(x, 1 ,3) as unsigned), x

so once again, i am asking @saravanacse1 to please state the requirement in words, not by example code (although example code is helpful)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.