Query to validate if selected data is numeric 1

[flstffatboy]

I'm trying to determine if there is a way to select data from a field and see if it is numeric. Is there any easy way to do this through script logic in SQL Plus?

Thank you,
FLSTF

 

[SantaMufasa]

Sure:

Section 1 -- Create a user-defined function:

create or replace function num_check (str_in varchar2) return varchar2 is
    num_hold number;
begin
    num_hold := str_in;
    return 'Y';
exception
    when others then
        return 'N';
end;
/

Section 2 -- Invoke the function:

col numeric format a7
select num_check(x)numeric,x from fatboy;

NUMERIC X
------- ------------
Y       01
Y       000005.44444
Y
N       123abcd
N       123 . 456

Let us know if this does the trick for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[flstffatboy]

Mufasa,

That worked great! Star for you.

Thanks
FLSTF