SQL command to get data type

[delorfra]

Hi,

I just want to extract part of a column string using substr and then check whether it has a numeric type, how can I do that in Oracle SQL ?

something like :

where IsNumeric(substr(myfield, 4,6))

Thanks.

 

[sem]

As far as I know there is no built-in function, but you may create your own. If you need to check whether your substring contains only digits the easiest way is to check for ltrim(your_string, '1234567890'), it should be null if your string is number.
If you need to now whether your string is valid number you may
create function IsNumber(pString in varchar2)
return boolean
as
dummy number;
begin
dummy := to_number(pString);
return true;
exception
when value_error then return false;
end;