Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
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');
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');
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Comments (0)