Prev: Oracle 10g on Windows connection problem - ORA-12505
Next: ORA 1801 - Dateformat too long for internal buffer
From: mr. ambastha on 24 Mar 2006 16:23
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

From: lisaashleyrafter on 24 Mar 2006 17:12
Hello there,
There is no built in function to accomplish that.
I suggest creating a utiilties package and then putting these types of
functions into that package (ie. isNumber(), etc).
Here's the pl/sql you'll need:
select instr(translate( <insert your variable/string here>
,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.

If you wanted to check if the string 'HELLO' was a number, it'd be used
like this:
select instr(translate( 'HELLO',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

Hope that helps,
Lisa Rafter

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

From: Mark D Powell on 25 Mar 2006 19:32
You could also just enclose a to_date function call within an exception
block. If the coversion is successful no error is returned and the
function could return the date. In the case of an error the exception
block would be raised and NULL, a defautl value, and/or an error code
could be returned depending on application specific needs.

HTH -- Mark D Powell --

From: Arto Viitanen on 27 Mar 2006 00:47
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
From: Malcolm Dew-Jones on 27 Mar 2006 12:03
Arto Viitanen (arto.viitanen(a)csc.fi) wrote:
: 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');

Regular expressions, very powerful, everyone should own one, but they are
also tricky, and easy to get wrong.

For example the above doesn't accomodate decimal points, and if it did it
probably wouldn't accomodate the language settings for them, not to
mention any other allowed representations of numbers. Not to say you
shouldn't use RE's, often you should, just realize that they can be
seductively simple but end up being a lot more work than you anticipated.


In this case I would go with to_number

-- untested
function is_a_number( s varchar2)
return number
is
begin
return to_number(s);
exception
-- This should check just the expected error
-- but I haven't bothered to look it up.
when others then
return NULL;
end is_a_number;


$0.10
 |  Next  |  Last
Pages: 1 2
Prev: Oracle 10g on Windows connection problem - ORA-12505
Next: ORA 1801 - Dateformat too long for internal buffer