Perhaps I'm understanding this incorrectly (and I think it got a
little confusing, because all of the ABC columns that matched preceded
the first "0") I read this as, you want to find the first occurance
of the "-0" and take the substring of the characters leading up to the
zero "0" (hypen appears to be included in your example). At that
point, find all matching records (text), up to that length.
I agree that a sort is required to determine the first occurance.
(note: I think Michael has provided an elegant solution - I'm just
looking at the question a different way). For this reason I included
an id column (sort column) to represent whatever you are sorting on.
To illustrate this, I used a 7th row (ending in 99), that again
matched.
create table t1 (id number, col1 varchar2(20));
insert into t1 values (1,'8-8-19-66-1-2-22');
insert into t1 values (2,'8-8-19-66-1-2-30');
insert into t1 values (3,'8-8-19-66-1-2-0');
insert into t1 values (4,'8-8-19-65-0-0-0');
insert into t1 values (5,'8-8-19-65-1-2-28');
insert into t1 values (6,'8-8-19-65-2-5-21');
insert into t1 values (7,'8-8-19-66-1-2-99');
select col1
from t1
,(select substr(col1,1,INSTR(col1||'-', '-0-')) new_string
,INSTR(col1||'-', '-0-') length_string
,ROW_NUMBER() OVER(ORDER BY id) row_number
from t1) a --inline view to control the string matching
,(select min(row_num) row_num
from (select ROW_NUMBER() OVER(ORDER BY id) row_num
,col1
,CASE WHEN instr(col1||'-', '-0-') > 0 THEN 1
ELSE 0
END has_zero
from t1
order by id)
where has_zero = 1) b --inline view to determine the first
string with a -0
where substr(t1.col1,1,a.length_string) = a.new_string
and a.row_number = b.row_num
COL1
--------------------
8-8-19-66-1-2-22
8-8-19-66-1-2-30
8-8-19-66-1-2-0
8-8-19-66-1-2-99
Output includes all records matching "8-8-19-66-1-2-".
-T
On May 13, 2:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
