/** source code **/
Create or Replace FUNCTION RET_NUMBER (ntext VARCHAR2) RETURN number IS
vnum number;
c number(1);
s varchar2(1):=null;
begin
–cari karakter desimal dulu
select instr(ntext,’,’) into c from dual;
if c = 0 then
select instr(ntext,’.’) into c from dual;
end if;
–jika ketemu ambil karakternya
if c > 0 then
s:= substr(ntext,c,1);
end if;
begin
select to_number(ntext) into vnum from dual;
exception when others then
begin
–jika error maka replace karakternya dari . menjadi , atau sebaliknya
select to_number(replace(ntext,s,decode(s,’,’,’.’,’,’))) into vnum from dual;
exception when others then
–jika masih salah maka munculkan error
RAISE_APPLICATION_ERROR(-20001, ‘Error konversi number ‘||ntext||’, cek BARIS KE atau kolom angka’);
end;
end;
return vnum;
end;
/** example **/
select ‘number with dot=’|| ret_number(’500.1′) ||’, number with coma ‘ || ret_number(’252,25′) from dual;
