select  regexp_replace('ham,src,account,number','[^,]+') rep_exc_comma,-- replace al with null excluding ,

regexp_replace('aeioAu:123456','[^:A-Z]+' ) rep_exc_colonatoz-- replace al with null excluding  : and A TO Z

from dual ; 

 

REP_EXC_COMMAREP_EXC_COLONATOZ
,,,A:

/

select  level , regexp_substr('ham,src,account,number','[^,]+',1,level),

regexp_replace('ham,src,account,number','[^,]+'),

length(regexp_replace('ham,src,account,number','[^,]+'))

from dual connect by level < length(regexp_replace('ham,src,account,number','[^,]+'))+2;  


LEVELREGEXP_SUBSTR('HAM,SRC,ACCOUNT,NUMBER','[^,]+',1,LEVEL)REGEXP_REPLACE('HAM,SRC,ACCOUNT,NUMBER','[^,]+')LENGTH(REGEXP_REPLACE('HAM,SRC,ACCOUNT,NUMBER','[^,]+'))
1ham,,,3
2src,,,3
3account,,,3
4number,,,3

Comments

Post a Comment

Popular posts from this blog

 declare   n  number; begin     select 1 into n from dual; if sql%found then -- if rows returned dbms_output.put_line('implicit1'||sql%rowcount);  end if; if sql%notfound then --if rows not returned dbms_output.put_line('implicit2'||sql%rowcount);  else  dbms_output.put_line('dump sql%notfound ' );  end if; if sql%isopen then --if cursor is in open state dbms_output.put_line('implicit3'||sql%rowcount);  else  dbms_output.put_line('dump sql%isopen' );  end if; end;
Read more
 declare  temp SYS_REFCURSOR; n  number; begin   -- open sys_refcursor for select statement open  temp for select level from dual connect by level < 4 ; loop -- cursor is in open state , cursor attributes are applicable   fetch temp  into n; -- fetch current record from temp assign to n      dbms_output.put_line('n'||n);    exit when temp%notfound;-- loop through the curesor until all the dataset exhausts end loop;   if  temp%found then      dbms_output.put_line('tempfound'||temp%rowcount);   end if;      if  temp%notfound then    dbms_output.put_line('tempnotfound'||temp%rowcount);   end if;     if  temp%isopen then    dbms_output.put_line('tempisopen'||temp%rowcount);   end if;      close temp;    end;
Read more
 declare   n  number; begin  -- Implicit cursor is opened , counter i copied the datatype as %rowtype fetch can't be used. for i in  (select level from dual connect by level < 4 ) loop  n :=i.level;   --fetch i into n;-- ORA-06550: line 10, column 9: PLS-00456: item 'I' is not a cursor     dbms_output.put_line('n'||n);    end loop; end;
Read more