Sort alphanumeric values in SQL Server

Databases
#22

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)

#23

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

#24

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)

#25

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