ORDER BY a combined value, text + numercial

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Published:
Updated:
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');

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

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');

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;

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.
4
1,756 Views
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community