Apologies - forgot to print the second column. The first line of the query should be:
select col1, substr(col1,1,length_string-1) new_col1 Gives: COL1 NEW_COL1 -------------------- ------------------- 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 8-8-19-66-1-2-99 8-8-19-66-1-2 On May 13, 3:00 pm, Trail <andrew.tr...@gnb.ca> wrote: > 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.- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
