How to sort rows in SQL that contain both numeric & alphanumeric values in ascending order?

Question

I have the following rows returned from my SQL query- can someone please help with a generic sorting code to sort the values in ascending order?

Also please note that my rows will be dynamically returned and will not always contain the same string as posted in my question above. It will be a mix of alphabets/integers. Below is just an example of a sample rows returned- need a generic formulae/sql approach and NOT a hard coding approach..thanks

('High_Speed'),
('M1 Speed'),
('M13 Speed'),
('M14 Speed'),
('M2 Speed'),
('M3 Speed'),
('Medium_Speed'),
('Test1 zone1 High_Speed'),
('Test1 zone11 High_Speed'),
('Test1 zone2 High_Speed'),
('Test1 zone21 High_Speed'),
('Zone206 Speed')

expected sorting-

('High_Speed'),
('M1 Speed'),
('M2 Speed'),
('M3 Speed'),
('M13 Speed'),
('M14 Speed'),
('Medium_Speed'),
('Test1 zone1 High_Speed'),
('Test1 zone2 High_Speed'),
('Test1 zone11 High_Speed'),
('Test1 zone21 High_Speed'),
('Zone206 Speed')

Logic is that the rows will be alphanumeric- so if you see the structure above- it will be sorted alphabetically first, within that alphabetic structure it needs to be sorted numerically like m1,m2,m3 etc. hope its clear now. — Rajeev, Jan 26 '20 at 13:35
Doing it purely in database independent SQL might be pretty hard, you should specify which engine you use. Maybe it has a "natural sort" function that can be used. — Hans-Martin Mosner, Jan 26 '20 at 13:38
@Rajeev . . . Then why is "High_Speed" first but "Medium_Speed" second to last. — Gordon Linoff, Jan 26 '20 at 13:42
@GordonLinoff apologies-I've edited and removed the duplicate 'Medium_speed' — Rajeev, Jan 26 '20 at 13:46
@Rajeev this old question may be useful: https://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005 — Hans-Martin Mosner, Jan 26 '20 at 14:31

VBoka · Accepted Answer · 2020-01-27T06:25:40.687

1

Try this:

select t_col from (
select t_col
       , LEFT(SUBSTRING(t_col, PATINDEX('%[a-z]%', t_col), LEN(t_col))
         , PATINDEX('%[^a-z]%', SUBSTRING(t_col, PATINDEX('%[a-z]%', t_col), LEN(t_col)))-1) col_col
       , convert(int, LEFT(SUBSTRING(t_col, PATINDEX('%[0-9]%', t_col), LEN(t_col))
         , PATINDEX('%[^0-9]%', SUBSTRING(t_col, PATINDEX('%[0-9]%', t_col), LEN(t_col)))-1)) as ord
from test) T1
order by col_col asc, ord asc, t_col asc;

Here is a demo

edited Jan 27 '20 at 06:25

answered Jan 26 '20 at 14:23

VBoka

8,995
3
16
24

thanks however if you see, m13,m14 still come before m2,m3. Requirement is like m1,m2,m3,m13,m14, – Rajeev Jan 27 '20 at 05:21
Hello @Rajeev, i was probably tired when I was posting this because your question is clear. I have updated my answer and I hope it will be ok now... Cheers – VBoka Jan 27 '20 at 06:26
Hello @VBoka , thanks as I can see it is working now- just wanted to check whether the above code is SPECIFICALLY for the data I have maintained OR it is a GENERAL code that will work with any alphanumeric data? – Rajeev Jan 27 '20 at 07:09
Hi @Rajeev , you are welcome. Happy to help. I have wrote this code to work on your examples but I am not sure are there some string + number examples when this will not work... Sorry. If you find them do contact me to check them and try to give you a new code... Also, you can find different functions that you can create and then use this functions in your code(function to get only numbers from string, function to get only characters from string) and then order by results.... If this works for you I would appreciate if you can mark the answer as corect... Thanks! Cheers! – VBoka Jan 27 '20 at 07:24
Hi @VBoka, thanks will connect with you. Actually my data set can be varied containing alphanumeric strings; and their lengths would be different, hence wanted to check with you. Because above code gives me the following error for the dataset (m1 speed,m13 speed,m14 speed,m2 speed, m3 speed, zone206 speed)that I've,...Invalid length parameter passed to the LEFT or SUBSTRING function.... – Rajeev Jan 27 '20 at 07:45
Please can you use the above demo to demonstrate your problem ? I will try to help. Thanks! – VBoka Jan 27 '20 at 07:47

How to sort rows in SQL that contain both numeric & alphanumeric values in ascending order?

1 Answers1