Re: numeric check

From: Arto Viitanen <arto.viitanen_at_csc.fi>
Date: Mon, 27 Mar 2006 08:47:58 +0300
Message-ID: <44277c8e$0$10075$ba624cd0@newsread.funet.fi>

mr. ambastha wrote:
> i just have a doubt. till date i was writing a pl sql function for
> checking whether the entered number is numeric or not. can anyone tell
> whether there is any built in function to check this? Earlier i was
> using isdigit() and isnumeric() in other languages. is this kind of
> functions available here?
> thanks in advance,
> regards,
> -ambastha
>

Newer version (10g ??) of Oracle has regular expression functions. So you could use following in SQL*Plus:

SQL> variable g_n varchar2(100)
SQL> set serveroutput on
SQL> begin

:g_n := '1222';
IF :g_n = regexp_substr(:g_n, '^[[:digit:]]+$') THEN

    dbms_output.put_line('OK');
END IF;
END;
/

It works at 10g1. The regular expression is:

  ^		beginning of the string
  [[:digit:]]	any character which belongs to category digit
  + 		one or more of the previous expresssion
  $		end of the string

regexp_substr returns substring which matches the expression. Since the expression contains beginning and end marks, the whole string has to match, otherwise it returns empty string.

(BTW.) regexp functions are SQL functions, but they can be used also in PL/SQL.

-- 
Arto Viitanen, CSC Ltd
Espoo, Finland

Received on Sun Mar 26 2006 - 23:47:58 CST