ORDER BY a combined value, text + numercial

Quite often, some product references (in a database) are combined from a textual prefix and a numerical suffix - like these values: AB123 and EF758...

Ordering them with a plain ORDER BY will be fine if the textual prefix  as well as the numerical suffixes have always the same length. However, when you have to order A123 and A32, you surely want A32 to come first, followed by A123. A straigthforward ORDER BY will not, however, be able to consider the "numerical" interpretation, and hence it returns results, for humans, in the wrong order.

How to solve it? Simply by splitting up the value into the two parts, and doing the ordering according to those two parts instead of the actual value. That said, most often, it would be better to have the two values split upfront and eventually have the combined value be built up in either the queries, the front-end or a computed column.

Code examples of splitting and sorting for MS SQL Server and Oracle are below. If you are using any other database system, you can surely find an adaptation to the code. The same "split" can be done in any programming language; these are just "quick" solutions. The path for the better solution as indicated above.

The issue with the splitting during the query is that the ORDER BY could not use any indexes for the ordering.

MS SQL Server

• creating the table :

create table my_ee ( value varchar(100));
                      insert into my_ee values ('A10');
                      insert into my_ee values ('B100');
                      insert into my_ee values ('A2');
                      insert into my_ee values ('AA1');
                      insert into my_ee values ('AD200');
                      insert into my_ee values ('32');
                      insert into my_ee values ('11');
                      insert into my_ee values ('A1');

Select all Open in new window

• running the SQL

select value
                      , left(value, patindex('%[0-9]%', value) -1 ) prefix
                      , cast(substring(value, patindex('%[0-9]%', value) , len(value)) as int) numvalue
                      from my_ee 
                      order by prefix, numvalue

Select all Open in new window

Oracle

• creating the table

create table my_ee ( value varchar2(100));
                      insert into my_ee values ('A10');
                      insert into my_ee values ('B100');
                      insert into my_ee values ('A2');
                      insert into my_ee values ('AA1');
                      insert into my_ee values ('AD200');
                      insert into my_ee values ('32');
                      insert into my_ee values ('11');
                      insert into my_ee values ('A1');

Select all Open in new window

• running the SQL

select value
                      , regexp_substr(value, '([^0-9]*)') prefix
                      , to_number(substr(value, nvl( 1+length(regexp_substr(value, '([^0-9]*)')),1),length(value))) numvalue
                      from my_ee 
                      order by prefix, numvalue;

Select all Open in new window

It is to note that on my installations, Oracle will sort the NULL values last, so the "numerical only" results will be found at the end, while SQL Server will sort those first. You can change that order as you need with some additional order by conditions.