Database Application Developer and DBA
ASK TOM BY TOM K Y TE
On Collaboration, Testing,
When, and Why
Our technologist learns from you, is tested on testing,
and propagates errors.
ORACLE DATABASE 11g
I am often asked how I got to know so much about Oracle Database. The answer is quite
simple: I learned everything I know because
of you—the readers of Ask Tom. It is through
the questions—and the research necessary to
answer those questions—that my knowledge
of the database has grown and continues to
grow. I’ve said many times that I learn something new about Oracle Database almost
every day, but another thing I’ve learned is
that sometimes the best answers come from
the readers of Ask Tom, not from me. Below is
one of those cases, and it shows how the open
forum approach—with the give and take from
readers—leads to the best answer possible.
Recently I was asked this question:
I have a varchar2 column in a table, and
its values contain only numbers and dots
(.). There are never two or more consecutive
dots, and the values represent versions. The
column data looks like this:
1
1. 1
1. 2
1. 10. 2
1. 1. 1
…
I want to sort this column by number
separated by dots. This is the result I want:
Is there any easy way to do this? I don’t
want to create a custom function to process
the column and then sort the values in the
SELEC T statement, such as SELEC T FROM
TableA ORDER BY functionA(ColumnA). Here
is my test data:
create table t ( col varchar2( 75) );
insert into t values (' 1');
insert into t values (' 1. 1');
insert into t values (' 1. 1. 1');
insert into t values (' 1. 1. 2');
insert into t values (' 1. 2');
insert into t values (' 1. 2. 4');
insert into t values (' 1. 2. 5');
insert into t values (' 1. 2. 10');
insert into t values (' 1. 10. 1');
insert into t values (' 1. 10. 2');
insert into t values (' 2');
insert into t values (' 2. 1');
insert into t values (' 22.333.1' )
Code Listing 1: First regular expression solution for sorting numbers and dots
SQL> select col,
2 regexp_replace
3 (col,'(^|\.)([[:digit:]]{ 3})','\1 \2') p1,
4 regexp_replace(
5 regexp_replace
6 (col,'(^|\.)([[:digit:]]{ 3})','\1 \2'),
7 '(^|\.)([[:digit:]]{ 2})','\1 0\2') p2,
8 regexp_replace(
9 regexp_replace(
10 regexp_replace
11 (col,'(^|\.)([[:digit:]]{ 3})','\1 \2'),
12 '(^|\.)([[:digit:]]{ 2})','\1 0\2'),
13 '(^|\.)([[:digit:]])','\1 00\2') p3
14 from t
15 order by
16 regexp_replace(
17 regexp_replace(
18 regexp_replace
19 (col,'(^|\.)([[:digit:]]{ 3})','\1 \2'),
20 '(^|\.)([[:digit:]]{ 2})','\1 0\2'),
21 '(^|\.)([[:digit:]])','\1 00\2');
COL P1 P2 P3
————————————————————————————— —————————————————————— —————————————————————— ————————————————————
1
1
1 001
1. 1 1. 1 1. 1 001.001
1. 1. 1 1. 1. 1 1. 1. 1 001. 001. 001
1. 1. 2 1. 1. 2 1. 1. 2 001. 001. 002
1. 2 1. 2 1. 2 001.002
1. 2. 4 1. 2. 4 1. 2. 4 001. 002. 004
1. 2. 5 1. 2. 5 1. 2. 5 001. 002. 005
1. 2. 10 1. 2. 10 1. 2. 010 001. 002. 010
1. 10. 1 1. 10. 1 1. 010.1 001. 010. 001
1. 10. 2 1. 10. 2 1. 010.2 001. 010. 002
2
2
2 002
2. 1 2. 1 2. 1 002.001
22.333.1 22. 333.1 022. 333.1 022. 333. 001
