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.
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
?
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
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
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 ?
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 ?
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?
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!
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!
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.