Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, John.

Asked: June 23, 2011 - 2:45 pm UTC

Last updated: April 25, 2012 - 3:16 pm UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a varchar2 column in a table, values only contain number and DOT and no two or more continuous DOTs. the value looks like following:
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
...

I want to sort this column by individual number separated by Dot. below is the result I want:
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2

NOT
1
1.1
1.1.1
1.1.2
1.10.1
1.10.2
1.2
1.2.10
1.2.4
1.2.5

Is there any easy way to do it? Can I just use any Oracle built-in functions to do it? But I don't want to create a customized function to process the column and then sort in the select statement, e.g. select * from TableA order by functionA(ColumnA).

create table t (col varchar2(30));
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');
commit;

Thanks.

and we said...

ops$tkyte%ORA11GR2> select col,
  2         to_number(substr( '.'||col||'.',
  3                   instr( '.'||col||'.', '.', 1, 1 )+1,
  4                   instr( '.'||col||'.', '.', 1, 2 )-instr( '.'||col||'.', '.', 1, 1 )-1 )) c1 ,
  5         to_number(substr( '.'||col||'.',
  6                   instr( '.'||col||'.', '.', 1, 2 )+1,
  7                   instr( '.'||col||'.', '.', 1, 3 )-instr( '.'||col||'.', '.', 1, 2 )-1 )) c2 ,
  8         to_number(substr( '.'||col||'.',
  9                   instr( '.'||col||'.', '.', 1, 3 )+1,
 10                   instr( '.'||col||'.', '.', 1, 4 )-instr( '.'||col||'.', '.', 1, 3 )-1 )) c3
 11    from t
 12   order by
 13         to_number(substr( '.'||col||'.',
 14                   instr( '.'||col||'.', '.', 1, 1 )+1,
 15                   instr( '.'||col||'.', '.', 1, 2 )-instr( '.'||col||'.', '.', 1, 1 )-1 ))
 16            nulls first,
 17         to_number(substr( '.'||col||'.',
 18                   instr( '.'||col||'.', '.', 1, 2 )+1,
 19                   instr( '.'||col||'.', '.', 1, 3 )-instr( '.'||col||'.', '.', 1, 2 )-1 ))
 20            nulls first,
 21         to_number(substr( '.'||col||'.',
 22                   instr( '.'||col||'.', '.', 1, 3 )+1,
 23                   instr( '.'||col||'.', '.', 1, 4 )-instr( '.'||col||'.', '.', 1, 3 )-1 ))
 24            nulls first
 25  /

COL                                    C1         C2         C3
------------------------------ ---------- ---------- ----------
1                                       1
1.1                                     1          1
1.1.1                                   1          1          1
1.1.2                                   1          1          2
1.2                                     1          2
1.2.4                                   1          2          4
1.2.5                                   1          2          5
1.2.10                                  1          2         10
1.10.1                                  1         10          1
1.10.2                                  1         10          2
2                                       2
2.1                                     2          1

12 rows selected.

Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John, June 24, 2011 - 1:39 pm UTC

Thanks Tom.

You assume the value only contains 3 levels of number, or maybe more e.g. 4 or 5. This may not work if you don't know the number of the levels.

I was thinking to use regular express functions (REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR), but I am not familiar with them. Do you think if it is feasible.

Thanks.
Tom Kyte
June 25, 2011 - 11:20 am UTC

well, I did ask you before answering "how many dots/levels" can there be. And I thought that by you hilighting:

...
number and DOT and no two or more continuous DOTs.
....

it was two dots.

You can either

a) add more substr's - as many as you need
b) use something like the other queries belong posted by others
c) try this:


....
ops$tkyte%ORA11GR2> insert into t values ('3.4.5.6.7.8.9.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0' );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> column p format a50 word_wrapped
ops$tkyte%ORA11GR2> column col format a20 word_wrapped
ops$tkyte%ORA11GR2> select col, max(sys_connect_by_path(v,'.')) p
  2    from (
  3  select t.col , to_number( substr(x.column_value,1,5) ) r, substr( x.column_value, 6 ) v, rowid rid
  4    from t, TABLE(
  5              cast(
  6                multiset(
  7                    select to_char( level, 'fm00000' ) ||
  8                           to_char(
  9                           to_number(substr( '.'||col||'.',
 10                                     instr( '.'||col||'.', '.', 1, rownum )+1,
 11                                     instr( '.'||col||'.', '.', 1, rownum+1 )-instr( '.'||col||'.', '.', 1, rownum )-1
 12                                     )), 'fm0000000000' )
 13                      from dual
 14                     connect by level <= length(col)-length(replace(col,'.',''))+1 ) as sys.odciVarchar2List ) ) x
 15         )
 16   start with r = 1
 17  connect by prior rid = rid and prior r+1 = r
 18  group by col
 19  order by p
 20  /

COL                  P
-------------------- --------------------------------------------------
1                    .0000000001
1.1                  .0000000001.0000000001
1.1.1                .0000000001.0000000001.0000000001
1.1.2                .0000000001.0000000001.0000000002
1.2                  .0000000001.0000000002
1.2.4                .0000000001.0000000002.0000000004
1.2.5                .0000000001.0000000002.0000000005
1.2.10               .0000000001.0000000002.0000000010
1.10.1               .0000000001.0000000010.0000000001
1.10.2               .0000000001.0000000010.0000000002
2                    .0000000002
2.1                  .0000000002.0000000001
3.4.5.6.7.8.9.1.2.3. .0000000003.0000000004.0000000005.0000000006.00000
4.5.6.7.8.9.0.1.2.3. 00007.0000000008.0000000009.0000000001.0000000002.
4.5.6.7.8.9.0.1.2.3. 0000000003.0000000004.0000000005.0000000006.000000
4.5.6.7.8.9.0        0007.0000000008.0000000009.0000000000.0000000001.0
                     000000002.0000000003.0000000004.0000000005.0000000
                     006.0000000007.0000000008.0000000009.0000000000.00
                     00000001.0000000002.0000000003.0000000004.00000000
                     05.0000000006.0000000007.0000000008.0000000009.000
                     0000000


13 rows selected.

creative connect by

Tyson Jouglet, June 24, 2011 - 5:05 pm UTC

You should not even consider using this. I just saw this as a sql challenge. It will work with up to 4 digit numbers. This can be increased at line 10.

SQL> select col from (
  2    select distinct col, root_sort from (
  3      select sort_col,
  4            ltrim(SYS_CONNECT_BY_PATH(sort_col,'.'),'.') root_sort,
  5            ltrim(SYS_CONNECT_BY_PATH(display_col,'.'),'.') root_display,
  6            ltrim(col,'.') col
  7        from(
  8          select distinct col,
  9                 substr(col,cur_dot + 1, decode(next_dot,0,length(col)+1, next_dot) - cur_dot -1) display_col,
 10                 lpad(substr(col,cur_dot + 1, decode(next_dot,0,length(col)+1, next_dot) - cur_dot -1),4,'0') sort_col,
 11                 next_dot,
 12                 cur_dot
 13            from(
 14              select distinct level lev,
 15                     col,
 16                     instr(col,'.',1,level) cur_dot,
 17                     instr(col,'.',instr(col,'.',1,level+1) ) next_dot
 18                from (select '.'|| col col from t_sort)
 19             connect by instr(col, '.', instr(col, '.',1, level )) > 0
 20            )
 21        )
 22      connect by nocycle prior next_dot = cur_dot
 23    )where root_display = col
 24  )
 25  order by root_sort
 26  ;

COL
-------------------------------
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2

COL
-------------------------------
2.1

connect a lot of DISTINCTs

Duke Ganote, June 25, 2011 - 7:59 am UTC

Fortunately, I didn't run out of fingers on one hand counting the number of DISTINCTs in Tyson Jouglet's query, but those indicate his words of caution are correct. I teased it apart into subqueries and removed the maximum length restriction ("It will work with up
to 4 digit numbers"):

with t_sort ( col ) as (
select '1' from dual union all
select '1.1' from dual union all
select '1.1.1' from dual union all
select '1.1.2' from dual union all
select '1.2' from dual union all
select '1.2.4' from dual union all
select '1.2.5' from dual union all
select '1.2.10' from dual union all
select '1.10.1' from dual union all
select '1.10.2' from dual union all
select '2' from dual union all
select '2.1' from dual
) , max_length as (
select max(length(col)) as max_col_length
from t_sort
), leading_dot as (
select '.'|| col as col, max_col_length
from t_sort
cross join max_length
) , dot_positions as (
select distinct level lev,
col,
instr(col,'.',1,level) cur_dot,
instr(col,'.',instr(col,'.',1,level+1) ) next_dot,
max_col_length
from leading_dot
connect by instr(col, '.', instr(col, '.',1, level )) > 0
order by col, lev
), dot_parts as (
select distinct col,
substr(col,cur_dot + 1
, decode(next_dot,0,length(col)+1, next_dot) - cur_dot -1)
AS display_col,
lpad(substr(col,cur_dot + 1
, decode(next_dot,0,length(col)+1, next_dot) - cur_dot -1)
,max_col_length
,'0')
AS sort_col,
next_dot,
cur_dot
from dot_positions
)
select col from (
select distinct col, root_sort from (
select sort_col,
ltrim(SYS_CONNECT_BY_PATH(sort_col,'.'),'.') root_sort,
ltrim(SYS_CONNECT_BY_PATH(display_col,'.'),'.') root_display,
ltrim(col,'.') col
from dot_parts
connect by nocycle prior next_dot = cur_dot
)where root_display = col
)
order by root_sort

NOTE: I ran this in 11gR2

very nice

Sokrates, June 25, 2011 - 1:12 pm UTC

****** (6 stars) for the query you gave in the
"Followup June 25, 2011 - 11am Central time zone"
!!!!!!!!!!!

very nice !

rownum ?

Sokrates, June 25, 2011 - 1:19 pm UTC

should
rownum
in lines 10 and 11 not better be replaced by
level
?
Tom Kyte
June 25, 2011 - 7:56 pm UTC

In this query, level = rownum = level

There is just one hierarchy per multi-set.

for consistency sake, it should be level throughout, yes.

regexp

Laurent Schneider, June 27, 2011 - 4:11 am UTC

If you accept a solution that will cover all cases between

0.0 and 999.999.999.999.999.999.999.999.999.999

I would consider
select * from t 
order by 
  regexp_replace(
    regexp_replace(
      regexp_replace(col,'(^|\.)(\d{3})','\1 \2'),
    '(^|\.)(\d{2})','\1 0\2'),
  '(^|\.)(\d)','\1 00\2')


John, June 27, 2011 - 7:38 am UTC

The solution below does not really work, "1.2.10" is at wrong order.

select * from t
order by
regexp_replace(
regexp_replace(
regexp_replace(col,'(^|\.)(\d{3})','\1 \2'), '(^|\.)(\d{2})','\1 0\2'),
'(^|\.)(\d)','\1 00\2')

regexp

Laurent Schneider, June 27, 2011 - 8:28 am UTC

Is it?

SQL> select * from t
  2  order by
  3    regexp_replace(
  4      regexp_replace(
  5        regexp_replace(col,'(^|\.)(\d{3})','\1 \2'),
  6      '(^|\.)(\d{2})','\1 0\2'),
  7    '(^|\.)(\d)','\1 00\2');

COL
------------------------------
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2
2.1
11.11

John, June 27, 2011 - 10:09 am UTC

COL
------------------------------
1
1.1
1.1.1
1.1.2
1.10.1
1.10.2
1.2
1.2.10
1.2.4
1.2.5
2
2.1

12 rows selected.

a bug in Oracle? I am using Version 10.1.4 for testing.

11gR2

Duke Ganote, June 27, 2011 - 3:03 pm UTC

11gR2 has ANSI-standard recursive common-table-expressions (CTEs) available. This query uses that feature. The query is not as concise as Tom's, of course.

with t as ( -- sample data
select '' col from dual where 1=0 union all
select '1' col from dual union all
select '1.1' from dual union all
select '1.1.1' from dual union all
select '1.1.2' from dual union all
select '1.2' from dual union all
select '1.2.4' from dual union all
select '1.2.5' from dual union all
select '1.2.10' from dual union all
select '1.10.1' from dual union all
select '1.10.2' from dual union all
select '2' from dual union all
select '3.4.5.6.7.8.9.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0'
from dual union all
select '2.1' from dual ),
tweak as ( -- modify to ease manipulations
select col
, '.'||col||'.' as recol
, length(col)-length(replace(col,'.',''))+1 parts
, row_number() over (order by col) as r#
from t
), max_parter as ( -- how many substrings and max length?
select max(parts) as max_parts
, max(length(col)) as max_length
from tweak )
, slicer(p#, max_length) AS ( -- helps "chop" substrings
SELECT 1 AS N
, max_length
FROM max_parter -- The "anchor"
UNION ALL
SELECT "prior".p# + 1 AS p# -- current
, "prior".max_length
FROM slicer "prior" -- The "recursive element"
WHERE "prior".p# < max_length
), sliced as ( -- chop it up and pad it out
select tweak.*, p#
, lpad(
substr(recol
,instr(recol,'.',1,p#)+1 -- start_position
,instr(recol,'.',1,p#+1)-1 --last_position
-( instr(recol,'.',1,p#) ) -- last-start = length
) --as parted
,max_length
,'0'
) as padded_part
from tweak
join slicer
on p# <= parts
), reassembly ( col, parts, r#, p#, padded_part, concat_part ) as (
select col, parts, r#, p#, padded_part
, padded_part as concat_part
from sliced
where r# = 1 and p# = 1
union all
select sliced.col
, sliced.parts, sliced.r#, sliced.p#, sliced.padded_part
, case when "prior".p# + 1 = sliced.p#
then "prior".concat_part||'.'||sliced.padded_part
else sliced.padded_part
end as concat_part
from reassembly "prior"
join sliced
on ( "prior".r# = sliced.r#
and "prior".p# + 1 = sliced.p# )
or ( "prior".r# + 1 = sliced.r#
and "prior".p# = "prior".parts
and 1 = sliced.p# )
)
select col--, concat_part
from reassembly
where p# = parts
order by concat_part
/
COL
-------------------------------------------------------------------------
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2
2.1
3.4.5.6.7.8.9.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0

Laurent Schneider, June 28, 2011 - 1:16 am UTC

@John, \d is a perl influenced regular expression and was introduced in 10.2

Try Posix

select * from t 
order by 
  regexp_replace(
    regexp_replace(
      regexp_replace(col,'(^|\.)([[:digit:]]{3})','\1 \2'),
    '(^|\.)([[:digit:]]{2})','\1 0\2'),
  '(^|\.)([[:digit:]])','\1 00\2');

John, June 28, 2011 - 7:19 am UTC

Thanks Laurent. It works on 10.1.4.

Saving trees

Brendan, June 29, 2011 - 1:31 am UTC

Laurent's solution is so simple I wondered if it could be generalised. I tried to left-pad each segment within a Regexp_Replace, but unfortunately Oracle seems to pad before substituting so it doesn't work:
SQL> SELECT ver, Regexp_Replace(ver, '(^|\.)(\d+)','\1' || LPad ('\2', 5, '0')) verp
  2    FROM test
  3   ORDER BY 2;

VER                  VERP
-------------------- --------------------
1                    0001
1.1                  0001.0001
1.1.1                0001.0001.0001
1.1.2                0001.0001.0002
1.10.1               0001.00010.0001
1.10.2               0001.00010.0002
1.2                  0001.0002
1.2.10               0001.0002.00010
1.2.4                0001.0002.0004
1.2.5                0001.0002.0005
2                    0002
2.1                  0002.0001

12 rows selected.

Can it be fixed?

The other approaches seem to involve a 'split-pad-join' process where the join uses a tree-walk or other recursion. Would this be a suitable case for the new 11.2.0.1 function ListAgg, which would seem more direct? Here is my solution (borrowing from several of the earlier ones):
SQL> WITH m AS (
  2  SELECT Max (Length (Regexp_Replace (t.ver, '\d', ''))) max_dots, 
  3         Max (Length (Regexp_Replace (t.ver, '\.', ''))) max_digs
  4    FROM test t
  5  ), p AS (
  6  SELECT t.ver, lev.ind, '.' || RPad (t.ver, Length(t.ver) + lev.max_dots + 1 - 
  7                                 Nvl (Length (Regexp_Replace(t.ver, '\d', '')), 0), '.') vpd
  8    FROM test t
  9   CROSS JOIN (
 10  SELECT level ind, max_dots
 11    FROM m
 12  CONNECT BY LEVEL <= max_dots + 1) lev
 13  )
 14  SELECT p.ver, p.vpd, 
 15          ListAgg(LPad (Nvl (Substr (p.vpd, Instr (p.vpd, '.', 1, p.ind)+1, 
 16          Instr (p.vpd, '.', 1, p.ind+1) - Instr (p.vpd, '.', 1, p.ind)-1), '0'), 
 17          m.max_digs, '0') ) WITHIN GROUP (ORDER BY p.ind) fld
 18    FROM m
 19   CROSS JOIN p
 20  GROUP BY p.ver, p.vpd
 21  ORDER BY 3;

VER             VPD             FLD
--------------- --------------- ---------------
1               .1...           000100000000
1.1             .1.1..          000100010000
1.1.1           .1.1.1.         000100010001
1.1.2           .1.1.2.         000100010002
1.2             .1.2..          000100020000
1.2.4           .1.2.4.         000100020004
1.2.5           .1.2.5.         000100020005
1.2.10          .1.2.10.        000100020010
1.10.1          .1.10.1.        000100100001
1.10.2          .1.10.2.        000100100002
2               .2...           000200000000
2.1             .2.1..          000200010000

12 rows selected.

Small simplification

Brendan, June 29, 2011 - 1:40 am UTC

WITH m AS (
SELECT Max (Length (Regexp_Replace (t.ver, '\d', ''))) max_dots,
Max (Length (Regexp_Replace (t.ver, '\.', ''))) max_digs
FROM test t
), p AS (
SELECT t.ver, lev.ind, '.' || RPad (t.ver, Length(t.ver) + lev.max_dots + 1 -
Nvl (Length (Regexp_Replace(t.ver, '\d', '')), 0), '.') vpd, lev.max_digs
FROM test t
CROSS JOIN (
SELECT level ind, max_dots, max_digs
FROM m
CONNECT BY LEVEL <= max_dots + 1) lev
)
SELECT p.ver, p.vpd,
ListAgg(LPad (Nvl (Substr (p.vpd, Instr (p.vpd, '.', 1, p.ind)+1,
Instr (p.vpd, '.', 1, p.ind+1) - Instr (p.vpd, '.', 1, p.ind)-1), '0'),
p.max_digs, '0') ) WITHIN GROUP (ORDER BY p.ind) fld
FROM p
GROUP BY p.ver, p.vpd
ORDER BY 3;

Another RegExp solution

Brendan, July 01, 2011 - 1:39 am UTC

Still trying to generalise Laurent's solution, this is the best I can do. It works for any number of segments, up to N characters in each, where N is the number of '.'s below (8 there) and there are N-1 '0's:
SELECT *
  FROM test
 ORDER BY RegExp_Replace (
  RegExp_Replace (ver || '.', '(\d+\.)', '0000000\1'),
  '0+(........)\.', '\1');

Laurent Schneider, July 01, 2011 - 6:55 am UTC

@brendan nice!!!

a usecase

Sokrates, July 08, 2011 - 1:48 am UTC

i just noticed that this sort criterion applies to sort Oracle-versions

for example

SELECT *
FROM v$system_fix_control
ORDER BY RegExp_Replace (
RegExp_Replace (optimizer_feature_enable || '.', '(\d+\.)', '0000000\1'),
'0+(........)\.', '\1'),
bugno

A small typo correction

Christian COMMARMOND, October 04, 2011 - 5:45 am UTC

Thanks to all for this lesson on regexp...

This part of the query: '0+(........)\.','\1'), wants to 'normalize' each part of the version to a constant length (8 Chrs) to obtain: 00000000.00000000. with version 10.2, you obtain: 0000001000000002. instead of 00000010.00000002.

I added a dot to obtain '\1.'), instead of '\1'), . That seems to solve the (small) issue.

So, here is the final(?) query:
SELECT optimizer_feature_enable, bugno, description
FROM v$system_fix_control
ORDER BY REGEXP_REPLACE (
REGEXP_REPLACE (optimizer_feature_enable || '.',
'(\d+\.)',
'0000000\1'),
'0+(........)\.',
'\1.'),
bugno;

Christian


nice thread

A reader, October 04, 2011 - 7:16 am UTC

... nice enough so that Tom writes about this on Oracle Magazine Nov/Dec 2011
Tom Kyte
October 04, 2011 - 11:38 am UTC

yeah, I'm not usually into regular expressions, but this one just caught my eye - much more elegant than my approach - and performs well enough on reasonable data sets..

A simpler version

Christian COMMARMOND, October 04, 2011 - 9:30 am UTC

One REGEXP_REPLACE less (saves some CPU cycles).

SELECT optimizer_feature_enable, bugno, description
FROM v$system_fix_control
ORDER BY REGEXP_REPLACE (replace('.'||optimizer_feature_enable,'.', '.0000000'),
'\.0+([=0123456789=]{8})',
'.\1') ,
bugno;

Is there a simple way to suppress the last REGEXP_REPLACE?

Christian

Amazing!

Ben, October 04, 2011 - 2:34 pm UTC

Tom and all the others on this forum are just amazing!
Thank you for sharing this. I was just reading Tom's column in the latest Oracle magazine and Tom has a link to this thread. Absolutely amazing!

@Christian

cd, October 14, 2011 - 7:11 am UTC

I did a piece on regexp a couple of years ago at https://forums.oracle.com/forums/thread.jspa?threadID=435109
and that was the shortest possible solution (in my opinion) I could come up with.


About order by rownum and multiset

orel, November 02, 2011 - 8:48 pm UTC

Hi Tom,

Please look at this query :

SQL> COLUMN list_c2 format A5
SQL> WITH t AS (
SELECT 'l1' AS c1, '1,8,3' AS c2 FROM dual union ALL
SELECT 'l2'      , '6,4'         FROM dual union ALL
SELECT 'l5'      , '5'           FROM dual union ALL
SELECT 'l6'      , '1,9,6,3'     FROM dual
),
       t2 as (
SELECT t.c1, x.column_value AS list_c2
  FROM t
 CROSS JOIN TABLE(
  cast (multiset(
         SELECT substr( ','||c2||',',
                  instr( ','||c2||',', ',', 1, rownum )+1,
                  instr( ','||c2||',', ',', 1, rownum+1 )
                   -instr( ','||c2||',', ',', 1, rownum )-1)
           FROM dual
        connect BY level <= length(c2)
                            -length(REPLACE(c2,',',''))+1
               ) AS sys.odcivarchar2list )
                ) x
)
select t2.*, 
       row_number() over(partition by t2.c1 
                             order by rownum) as rn
  from t2
/

C1 LIST_         RN
-- ----- ----------
l1 1              1
l1 8              2
l1 3              3
l2 6              1
l2 4              2
l5 5              1
l6 1              1
l6 9              2
l6 6              3
l6 3              4

10 rows selected.

Sure it's look to work but with RDBMS "looks to" is definitely not enough.

This query may be safe because we pick the number one by one, but the CROSS JOIN part worryies me.

Do you consider the "order by rownum" part of row_number is safe ?

Tom Kyte
November 03, 2011 - 1:10 am UTC

... Do you consider the "order by rownum" part of row_number is safe ?
...

does it matter in this case? You are just ordering by 'random' as far as I'm concerned. the rownum will be assigned arbitrarily to the rows in the set unless you sort the set somehow, but - if you can sort the set using an order by - you could just put those attributes right in the row_number order by.

I don't get using rownum in this context, can you explain what you are trying to accomplish?

maybe a better approch

orel, November 03, 2011 - 3:31 pm UTC

Thanks Tom for your feedback.
I'm sorry, I forgot to describe the problem.
The idea is to get a rn column ordering each number by their position in the string.

So you confirmed what I was worried about, this "order by rownum" is just an "order by random".
Thanks.

I think a better approch would be to create my own type to store level, like this :

SQL> create or replace type myObj as object (lvl number, str varchar2(4000))
  2  /

Type created.

SQL> create or replace type myTab as table of myObj
  2  /

Type created.

SQL> WITH t AS (
  2  SELECT 'l1' AS c1, '1,8,3' AS c2 FROM dual union ALL
  3  SELECT 'l2'      , '6,4'         FROM dual union ALL
  4  SELECT 'l5'      , '5'           FROM dual union ALL
  5  SELECT 'l6'      , '1,9,6,3'     FROM dual
  6  ),
  7         t2 as (
  8  SELECT t.c1, x.str AS list_c2, x.lvl as rn
  9    FROM t
 10   CROSS JOIN TABLE(
 11    cast (multiset(
 12           SELECT level,
 13                  substr( ','||c2||',',
 14                    instr( ','||c2||',', ',', 1, rownum )+1,
 15                    instr( ','||c2||',', ',', 1, rownum+1 )
 16                     -instr( ','||c2||',', ',', 1, rownum )-1)
 17             FROM dual
 18          connect BY level <= length(c2)
 19                              -length(REPLACE(c2,',',''))+1
 20                 ) AS myTab )
 21                  ) x
 22  )
 23  select t2.*
 24    from t2
 25  /

C1 LIST_         RN
-- ----- ----------
l1 1              1
l1 8              2
l1 3              3
l2 6              1
l2 4              2
l5 5              1
l6 1              1
l6 9              2
l6 6              3
l6 3              4

10 rows selected.


Do you think rn column is "safe" now ?

Anyway, this is not a real life problem. It was more a having fun problem playing around SQL :
http://beyondrelational.com/puzzles/challenges/101/find-the-longest-sequence-of-alphabets-in-a-string.aspx

Don't you think it would be cool if there was this kind of SQL challenge in Oracle community ?

Tom Kyte
November 04, 2011 - 1:09 am UTC

either

a) concatenate the rownum in in the first place and substr it out later
b) create an object type that has two attributes - a number and a varchar2(1) and return that from your multiset.


Thanks

orel, November 04, 2011 - 8:57 pm UTC

Thanks,
I like a/ because it fits more the challenge's context.
But b/ is more the way to go in real life problem (and yes varchar2(1), definitely no need for varchar2(4000)...)

Variant on Tom's approach

Mark Wooldridge, November 14, 2011 - 4:37 pm UTC

create type vc4000_ttyp as table of varchar2(4000)
/

select val,
(select to_string(cast(collect(
lpad(substr(val,
decode(level,
1, 1,
instr(val, '.', 1, level-1)+1),
instr(val, '.', 1, level)-decode(level, 1, 1, instr(val, '.', 1, level-1)+1)),
6, '0')) as vc4000_ttyp), '.') val2
from dual
connect by level <= length(translate(val, '.0123456789', '.'))) val3
from (select col||'.' val from t)
order by 2
/

very interesting

mike, December 21, 2011 - 1:20 pm UTC

My instinct would have been to design the table with separate columns for major version, minor version, etc. and use a virtual column to concatenate them with dots. Would that approach be inferior in some way?
Tom Kyte
December 21, 2011 - 3:04 pm UTC

depends, do you have a fixed number of dots?

it certainly would be easier if they were separate :)

How to handle this senario..

Ravi B, April 23, 2012 - 12:06 pm UTC

Hi Tom,

I have been trying the queries suggested by you and many people but unable to come up with the scenario which has the following data.

create table tmp_ver (col varchar2(30));

insert into tmp_ver values ('1');
insert into tmp_ver values ('1.1');
insert into tmp_ver values ('1.1.1');
insert into tmp_ver values ('1.1.2');
insert into tmp_ver values ('1.2');
insert into tmp_ver values ('1.2.4');
insert into tmp_ver values ('1.2.5');
insert into tmp_ver values ('1.2.10');
insert into tmp_ver values ('1.10.1');
insert into tmp_ver values ('1.10.2');
insert into tmp_ver values ('2');
insert into tmp_ver values ('2.1');
insert into tmp_ver values ('3.52');
insert into tmp_ver values ('3.202');
insert into tmp_ver values ('1.4.32');
insert into tmp_ver values ('1.4.213');


I tried the following queries.

SELECT col,RegExp_Replace (
  RegExp_Replace (col || '.', '(\d+\.)', '0000000\1'),
  '0+(........)\.', '\1') reg
  FROM tmp_ver
 ORDER BY RegExp_Replace (
  RegExp_Replace (col || '.', '(\d+\.)', '0000000\1'),
  '0+(........)\.', '\1');


select col, regexp_replace(col,'(^|\.)(\d{3})','\1 \2') one,
       regexp_replace(
          regexp_replace(col,'(^|\.)(\d{3})','\1 \2'),
        '(^|\.)(\d{2})','\1 0\2') two,
        regexp_replace(
        regexp_replace(
          regexp_replace(col,'(^|\.)(\d{3})','\1 \2'),
        '(^|\.)(\d{2})','\1 0\2'),
      '(^|\.)(\d)','\1 00\2') three
from tmp_ver
order by regexp_replace(
          regexp_replace(
            regexp_replace(col,'(^|\.)(\d{3})','\1 \2'),
          '(^|\.)(\d{2})','\1 0\2'),
          '(^|\.)(\d)','\1 00\2') asc;


SELECT col
   FROM tmp_ver
ORDER BY REGEXP_REPLACE (replace('.'||col,'.', '.0000000'),
                            '\.0+([=0123456789=]{8})',
                            '.\1') ;


Issue:

1.4.32 comes before 1.4.213 where as 1.4.32 should be greater than 1.4.213

3.52 comes before 3.202 whereas 3.52 should be greater than 3.202.


Could you please help me modify this query to handle this?

Thanks!
Tom Kyte
April 24, 2012 - 7:25 am UTC

1.4.32 comes before 1.4.213 where as 1.4.32 should be greater than 1.4.213 ,

You'll really have to explain that logic to me - I cannot imaged 1.4.32 (32) being greater than 1.4.213 (213)





http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61asktom-512015.html

ops$tkyte%ORA11GR2> 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 tmp_ver
 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.4.32     1.4.32     1.4. 032    001. 004. 032
1.4.213    1.4. 213   1.4. 213    001. 004. 213
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
3.52       3.52       3. 052      003. 052
3.202      3. 202     3. 202      003. 202

16 rows selected.

How to handle this senario..

Ravi B, April 25, 2012 - 2:56 pm UTC

Hi Tom,

This came from one of our analyst saying that the ordering was wrong. Now he is convinced that what the query does is correct.

I have a question myself about how this sorting really works.
I understand that regexp_replace is padding zeros to the digits but the decimal still remains. How could the query do a sort the string as if it is a numeric sort?

Thanks!
Tom Kyte
April 25, 2012 - 3:16 pm UTC

if you make the numbers be right justified in a fixed width field - they sort correctly as numbers even though the are in strings.


for the same reason many people are tempted to store dates in a number using the YYYYMMDD format - or in a string using the same. The number/string 'accidentally' sort like a date would - even though they are not dates.


The number 1, 10, 100 in a fixed width, right justified field would be:
001
010
100


and that sorts correctly as a string - because of the collating sequence of "numbers" in strings.