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 -~----------~----~----~----~------~----~------~--~---