Home » SQL & PL/SQL » SQL & PL/SQL » Re: Find Numbers
Re: Find Numbers [message #1842] Tue, 04 June 2002 05:20 Go to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Tina,

here is the changed code.
this will NOT RIP any ZEROS...
anyhow, i am still looking into your requirement, that NOT TO STRIP ANY ZEROS within the 8 digits. any more sample data's will be more helpful.
SQL> get rip_number;
  1  create or replace function rip (f_in varchar2)
  2  return varchar2 as
  3  v_in varchar2(30);
  4  opt varchar2(30);
  5  retval varchar2(30);
  6  dumm varchar2(30);
  7  begin
  8  v_in:=replace(f_in,' ');
  9  for mag in 1..length(v_in) loop
 10     opt:=substr(v_in,mag,1);
 11     if is_number(opt,1,1)='Y'  then
 12     dumm:=dumm||opt;
 13     end if;
 14     end loop;
 15  return dumm;       
 16* end;
SQL> /

Function created.

SQL> select rip(col1) from test10;

RIP(COL1)
--------------------------------------------------------------------------------
0078965432
24579893
23794832
0078434521

SQL> 
Re: Find Numbers [message #1855 is a reply to message #1842] Tue, 04 June 2002 23:39 Go to previous message
Tina
Messages: 38
Registered: November 2001
Member
Mahesh,
Once again thank you for your help. I'm sorry that I can not provide you with more information. The problem being that this field can contain any data, not just the eight digit figure that I require. It's a VARCHAR2 field which allows the user to enter anything. If there is an eight digit string, I need to strip it out. Therefore I can't supply you with every possible combination of data that might populate this field.

Thanks again!
Previous Topic: Dynamic creation of Table using triggers.
Next Topic: How to simulate initcap function
Goto Forum:
  


Current Time: Tue Nov 13 13:58:32 CST 2018