Testing for Numeric Values inside a varchar2 field type 1

[mozgheib]

Hello,

I have a varchar2 field that has values
that are sometimes not numeric. However,
I am only interested in numeric values
since there are records that have numeric
values in that field.

SO I would like to test that field values
and only return the numeric values.

I was wondering what should my syntax look
like.

Thanks.

 

[lewisp]

The most reliable way is to create a function.

CREATE FUNCTION isnum (p_char IN VARCHAR2)
RETURN NUMBER IS
  n1 NUMBER;
BEGIN
  n1 := To_Number(p_char);
  Return 0;
EXCEPTION
  WHEN OTHERS
  THEN
    Return 1;
END;

Then you can use it in SQL:

SELECT x
FROM   y
WHERE  isnum(z) = 0;

 

[mozgheib]

thanks it works fine for me.