<

ORDER BY a combined value, text + numercial

Published on
5,743 Points
1,343 Views
4 Endorsements
Last Modified:
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
Comment
0 Comments

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month