<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

ORDER BY a combined value, text + numercial

Published on
5,845 Points
1,445 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

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Join & Write a Comment

Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month