1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
|
CREATE OR replace function NATURAL_ORDER(
P_STR varchar2
) return varchar2
IS
/** --------------------------------------------------------------------
Replaces all sequences of numbers shorter than 10 digits by 0-padded
numbers that exactly 10 digits in length. Usefull for ordering-by
using NATURAL ORDER algorithm.
*/
l_result varchar2( 32700 );
l_len integer;
l_ix integer;
l_end integer;
begin
l_result := P_STR;
l_len := LENGTH( l_result );
l_ix := 1;
while l_len > 0 loop
l_ix := REGEXP_INSTR( l_result, '[0-9]{1,9}', l_ix, 1, 0 );
EXIT when l_ix = 0;
l_end := REGEXP_INSTR( l_result, '[^0-9]|$', l_ix, 1, 0 );
if ( l_end - l_ix >= 10 ) then
l_ix := l_end;
else
l_result := substr( l_result, 1, l_ix - 1 )
|| LPAD( SUBSTR( l_result, l_ix, l_end-l_ix ), 10, '0' )
|| substr( l_result, l_end )
;
l_ix := l_ix + 10;
end if;
end loop;
return l_result;
end;
/ |
Partager