Use of Translate and storing in number column
616912Feb 13 2009 — edited Mar 8 2009Hello,
we have a column containing mobile no in varchar format which also contains space or special charcters.
To remove special charcter i call ed a function like this
CREATE OR REPLACE function Conv_mobile
(mobile_no in cams_folio.MOBILE_NO%type )
return number
as
begin
return to_number (mobile_no);
exception when others then return(translate(MOBILE_NO,'+!@#$%^&*()''-','0123456789'));
end;
/
But the problem is that i have to store this value in number column
so when update the column the function gives error.
Is there anyway to remve special charcters and store them in number column which is 13 digit length.