so = whatever you plan on sorting on
select * from mikejunk;
WITH qrs AS
(SELECT abc,
ROW_NUMBER () OVER (ORDER BY so) rownumber
FROM mikejunk)
select i.abc,chop from qrs i join (
SELECT rownumber,
abc,
SUBSTR (abc, 1,
COALESCE ( NULLIF (0, REGEXP_INSTR (abc, '(^0-)'))
+ 1, REGEXP_INSTR (abc, '(-0-)|(-0$)'))) chop
FROM qrs z
WHERE rownumber = (SELECT MIN (rownumber)
FROM qrs x
WHERE (abc LIKE '0-%' OR abc LIKE '%-0-%' OR abc LIKE
'%-0'))) m
on i.rownumber <= m.rownumber ;
ABC SO
---------------- ----------
8-8-19-66-1-2-22 1
8-8-19-66-1-2-30 2
8-8-19-66-1-2-0 3
8-8-19-65-0-0-0 4
8-8-19-65-1-2-28 5
8-8-19-65-2-5-21 6
6 rows selected.
ABC CHOP
---------------- ----------------
8-8-19-66-1-2-22 8-8-19-66-1-2-
8-8-19-66-1-2-30 8-8-19-66-1-2-
8-8-19-66-1-2-0 8-8-19-66-1-2-
3 rows selected.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
