simple, instant response
A reader, February 02, 2004 - 8:13 am UTC
Thanks, couldn't be more precise!
Perhaps I'm being picky but...
Mike, February 02, 2004 - 8:18 am UTC
I seem to remeber some highly regarded Oracle person saying that it is a bug if you use a "when others" that is not followed by a raise...
Shurley it should be a "when invalid number" and or when numeric or value error that should be used, or you could get any other error and wrongly assume that its because it is not a number ;)
February 02, 2004 - 9:24 am UTC
I said it almost certainly is a bug -- not 100%, almost certainly :)
Here -- we are in FACT expecting an exception to be raised
AND
we can deal with it
MEANING
it isn't an error but an expected "condition" -- like NO_DATA_FOUND. There are two exceptions that could be raised -- hence the use of when others here -- since the ONLY thing that could be going wrong here is in fact "invalid number".
Being picky...
Mark A. Williams, February 02, 2004 - 9:25 am UTC
Surely you did not mean "shurley"? (My typing is not better, btw)
But, on a more serious note, who is to say that "...code that should run when it is in fact NOT a number..." does not include a RAISE?
- Mark
I did mean Shurley
A reader, February 02, 2004 - 9:40 am UTC
I was refering to a "Shurley Shome Mishtake" from Private Eye a satirical mag that you probably don't see a lot of outside of britain (but should)
however I do agree that any of the code included could raise an error and cause the code to switch to thinking it's not a valid number. another block would be needed. but I think this is getting quite pedantic now...
Shurley!
Mark A. Williams, February 02, 2004 - 9:46 am UTC
My mistake (I should know better - my wife is from Buckinghamshire). Anyway, pedantic can be good sometimes too! (But I agree)
Cheers,
Mark
What is a number
Fan, February 02, 2004 - 12:39 pm UTC
I recall in another forum a discussion about creating a function to determine if a value is a number, and in the end, we decided, only the customer/coder can determine what a number is
It came down to creating test data (and a fat finger), to discover that not all numbers look like numbers. e.g., take the values 1500, 15e00 and 15.00 we know that they are numbers (not all necessarily look like numbers). The question is:
Does the customer want them ALL treated as numbers?
I beg to differ...
Mike, February 20, 2004 - 9:58 am UTC
Perhaps it;s because it's Friday, but I seem to be deluded into thinking I might be right.
Consider...
declare
l_string varchar2(10) := '123';
l_test_number number;
begin
begin
dbms_output.put_line('Examine String');
l_test_number := l_string;
dbms_output.put_line('It is a number');
--code that should run when it is in fact a number......
--
-- lots of lots of code, perhpas it inserts into a table and violates a check constraint or PK? or as
--below does a select into that raises a NO_DATA_FOUND
raise no_data_found;
exception
when others
then
dbms_output.put_line('It is not a number');
--code that should run when it is in fact not a number.....
end;
--
end;
How can this code not "go wrong" without changing the when others? in other words how can you account for an exception being raised after the check and it being handled correctly?? set some flags to know you went past the number check? The best I can think of is
declare
l_string varchar2(10) := '123';
l_test_number number;
begin
begin
dbms_output.put_line('Examine String');
l_test_number := l_string;
dbms_output.put_line('It is a number');
--code that should run when it is in fact a number......
--
-- lots of lots of code, perhpas it inserts into a table and violates a check constraint or PK? or as
--below does a select into that raises a NO_DATA_FOUND
raise no_data_found;
exception
when others
then
-- we must test both varsincase l_string
-- was passed as null and we then raised an
-- error, null is a valid number
if l_test_number is not null
or l_string is null
then
raise;
else
dbms_output.put_line('It is not a number');
--code that should run when it is in fact not a number.....
end if;
end;
--
end;
and I'm not really a fan of that ;)
February 20, 2004 - 10:12 am UTC
You are right, it was a bit sloppy -- i would normally create a function "is_number" that returns boolean but short of that,
declare
l_string varchar2(10) := '123';
l_test_number number;
l_is_number boolean := true;
begin
begin
l_test_number := l_string;
exception
when others then
l_is_number := FALSE;
end;
if ( l_is_number )
then
--code that should run when it is in fact a number......
--
else
--code that should run when it is in fact not a number.....
end;
end;
would be "safe"
Check if numeric
Martin, February 20, 2004 - 11:03 am UTC
Hi,
Although not very readable this seems to work fine:
replace(translate(<value>,'N01234567890','XNNNNNNNNNN'),'N',null)
If this expression is null then the value is a number, otherwise it's not.
February 20, 2004 - 1:07 pm UTC
a whole number sure, what about -1.12543
Vicky, March 11, 2004 - 8:03 pm UTC
This should do for negative and numbers with decimal points.
replace(replace(replace(translate(contact_value,'N01234567890','XNNNNNNNNNN'),'N',null),'-'),'.')
What about values like 1.2.3 or --1
A reader, March 19, 2004 - 12:30 am UTC
cheers
March 19, 2004 - 8:57 am UTC
use the exception block, it'll catch absolutely EVERYTHING.
alternate idea
Tim W, March 29, 2004 - 5:54 pm UTC
This is more of an algorithm than oracle code, because i have only been working with oracle for about a month now. But it should take any alphanumeric string and report if it is a number.
Given T is a string of characters.
if (length(T) = length(replace(T,'abcdefghijklmnopqrstuvyxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+=`[]{}\|;:,<>/?'))
{
// the string contains only 0-9 . and -
if( (length(T) - length(replace(T,'.'))) <= 1)
{
// the string contains only 0 or 1 "."
if( (length(T) - length(replace(T,'-')) ) = 0) OR ( (length(T) - length(replace(T,'-')) = 1) AND substr(T,1,1) = "-" )
{
// the string contains 0 or 1 "-" and if so it is in the first position
execute true statements here
}
}
}
the reason for this one is that the prior sugestion will accept 9-9.-9.9 as a valid string which it is not. because a number may contain no more than one "." and no more than one "-" which must be in the first position only. thus comparing the lengths of the replaced strings.
March 29, 2004 - 6:15 pm UTC
how about:
declare
l_num number;
begin
l_num := to_number(T);
code for when t is a number
exception
code for when t is not a number
end;
or even "easier"
create function isnumber( p_string in varchar2 ) return boolean
is
l_number number;
begin
l_number := P_string;
return true;
exception
when others then return false;
end;
/
begin
if (isnumber(T))
then
.....
Built in function
Tim W, March 30, 2004 - 8:49 am UTC
Well if there is a built in function use that. No need to re-invent the wheel. The impression i got was that there wasnt one built in.
March 30, 2004 - 9:03 am UTC
declare
l_num number;
begin
l_num := to_number(T);
code for when t is a number
exception
code for when t is not a number
end;
"to_number" is the builtin. 'isnumber' makes it easier to use...
One simple question
A reader, January 26, 2006 - 9:38 am UTC
Tom,
I have a column that I would like to provent the users
from putting decimals it must be whole number. Is there
anyway I can accomplish that?
Thanks you.
January 26, 2006 - 10:25 am UTC
use number(n)
no decimals allowed - it'll round the number upon input.
if you want to fail the transaction, you can just use number and add a check constraint to the column:
check ( x = trunc(x) )
Check constraint?
Mike Walsh, January 27, 2006 - 12:58 am UTC
create table foon
(bar number(3) check (bar=trunc(bar)));
-- Table created.
insert into foon(bar) values (25.2323);
-- 1 row created.
select bar from foon;
-- BAR
-- ----------
-- 25
select 1 from dual where 21=trunc(21);
-- 1
-- ----------
-- 1
select 1 from dual where 21.0000001=trunc(21.0000001);
-- no rows selected
select 1 from dual where
21.00000000000000000000000000000000000000001=
trunc(21.00000000000000000000000000000000000000001);
-- 1
-- ----------
-- 1
set serverout on size 100000
create or replace trigger foon_ck before insert on foon for each row
begin
if (:new.bar <> trunc(:new.bar)) then
raise_application_error(-20002,'Toast');
else
dbms_output.put_line('Value of bar[' || :new.bar || ']');
end if;
end foon_ck;
/
show errors;
-- Trigger created.
-- No errors.
insert into foon(bar) values (25.2323);
-- Value of bar[25]
-- 1 row created.
--------
Please excuse the quick and dirty example, but what am I
missing?
Clearly there's a rounding issue to deal with also, but
both the check and trigger seem to access the post-rounded
value...
January 27, 2006 - 8:36 am UTC
you made it a number(3), that is going to round (as I stated)
I said "use number(n)" and it'll round
OR
I said "just use number and this check constraint"
ops$tkyte@ORA9IR2> create table t
2 ( x number(3),
3 y number check (y=trunc(y))
4 )
5 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1.2, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( null, 1.2 );
insert into t values ( null, 1.2 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C003710) violated
ops$tkyte@ORA9IR2> select * from t;
X Y
---------- ----------
1
Reading too late... but raises another question.
Mike Walsh, January 27, 2006 - 11:07 am UTC
Sorry, Tom. I should read more carefully. A little too subtle
a difference for me after midnight.
I'm wondering if you can explain this related plsql issue:
create table foon
(bar number(3));
-- Table created.
set serverout on size 100000
create or replace procedure rounded(val in foon.bar%type)
as
val2 foon.bar%type;
begin
dbms_output.put_line(val);
begin
val2 := val;
dbms_output.put_line(val2);
exception
when others then
dbms_output.put_line(sqlcode || '-' || sqlerrm);
end;
end rounded;
/
-- Procedure created.
exec rounded(25.2323);
-- 25.2323
-- 25
-- PL/SQL procedure successfully completed.
exec rounded(25000);
-- 25000
-- -6502-ORA-06502: PL/SQL: numeric or value error: number precision too large
-- PL/SQL procedure successfully completed.
Why do the two usages get treated differently? I have a
guess (you get a chance at error recovery with internal
values vs. arguments), but I'm not sure.
Thanks.
January 27, 2006 - 11:32 am UTC
all parameters types in plsql are unconstrained types - I concurr the documentation does not relate this very well, but they are unconstrained types - always.
unconstrained types means..?
A reader, January 27, 2006 - 11:56 am UTC
What does that mean actually? Does it mean that even if we define a variable as number(3), pl/sql will take it as just number without a limit?
January 28, 2006 - 12:08 pm UTC
No, a variable is constrained, parameters - are not.
ops$tkyte@ORA10GR1> create table t (x number(3) );
Table created.
ops$tkyte@ORA10GR1> create or replace procedure p( unconstrained t.x%type )
2 as
3 constrained t.x%type := 1.2;
4 begin
5 dbms_output.put_line( unconstrained );
6 dbms_output.put_line( constrained );
7 end;
8 /
Procedure created.
ops$tkyte@ORA10GR1> exec p( 1.2 )
1.2
1
PL/SQL procedure successfully completed.
unconstrained...
Mike Walsh, January 27, 2006 - 12:31 pm UTC
He means that arguments are typed but not sized, which is
how you define them without the "%type":
procedure do_stuff(count in number,
tags in varchar2,
sized in number(3)) <-- this would fail
whereas with
procedure do_stuff(count in number,
tags in varchar2,
sized in foon.bar%type)
the argument "sized" implicitly gets rewritten as
sized in number
rather than
sized in number(3)
which would actually match the table. It's consistent
with the way it's documented (Ch 8 - PL/SQL Subprograms:
"You cannot constrain the datatype of a parameter.")
It's just not obvious (at least not to me), especially
when inside the procedure it is constrained.
I often forget about constraints, since
procedure do_stuff(count in number)
as
v_other number;
...
is fine, but
procedure do_stuff(msg in varchar2)
as
v_other varchar2;
...
complains that
Error: PLS-00215: String length constraints must be in range (1 .. 32767)
Of course it's because number has a default size and
varchar2 doesn't, but still, it doesn't scan very well...
To Mike
Greg, January 27, 2006 - 12:52 pm UTC
Great job explaining that. You gave clear examples and references to the documentation. Well Done!!
Any easy way to handle formatted numbers?
Greg, February 15, 2006 - 9:53 am UTC
gregs-DEV > create or replace function isnumber( p_string in varchar2 ) return boolean
2 is
3 l_number number;
4 begin
5 l_number := P_string;
6 return true;
7 exception
8 when others then return false;
9 end;
10 /
Function created.
gregs-DEV >
gregs-DEV > set serverout on
gregs-DEV >
gregs-DEV > begin
2 if isnumber('45,456.56') then
3 dbms_output.put_line ( 'Number' );
4 else
5 dbms_output.put_line ( 'Not a number' );
6 end if;
7 end;
8 /
Not a number
PL/SQL procedure successfully completed.
??
'45,456.56' is a number .. it's just formatted .. the coma's confusing it ...
If I change the function to:
5 l_number := to_number ( P_string, '999,999.99' );
it works .. but then '45456.56' doesn't work!
How can I "have my cake, and eat it too"?? ;)
(and ... I don't really want to strip out comas, because: "34,34,34.45" isn't a number . it looks more like a list of numbers ... )
or is it simply: "we gotta know the format" ??
February 15, 2006 - 11:45 am UTC
could do something like this:
ops$tkyte@ORA10GR2> create or replace function isnumber( p_string in varchar2 ) return boolean
2 is
3 not_number exception;
4 pragma exception_init( not_number, -6502 );
5 l_number number;
6 l_return boolean := FALSE;
7 begin
8 if (instr(p_string,',') > 0 )
9 then
10 l_number := to_number( p_string, '999g999g999g999g999g999d999999999999999999' );
11 else
12 l_number := to_number( p_string );
13 end if;
14 return TRUE;
15 exception
16 when not_number
17 then
18 return FALSE;
19 end;
20 /
Function created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
2 type array is table of varchar2(255);
3 l_data array := array( '123456.01', '123,456.01', '12,34,56', 'abc' );
4 begin
5 for i in 1 .. l_data.count
6 loop
7 if ( isnumber( l_data(i) ) )
8 then
9 dbms_output.put_line( l_data(i) || ' yes' );
10 else
11 dbms_output.put_line( l_data(i) || ' no' );
12 end if;
13 end loop;
14 end;
15 /
123456.01 yes
123,456.01 yes
12,34,56 no
abc no
PL/SQL procedure successfully completed.
*blink*
Greg, February 15, 2006 - 1:08 pm UTC
Right ... for some reason I was trying to challenge myself and not use an IF or INSTR statement ... ;p
Silly me ...
Thanks!!
February 15, 2006 - 1:22 pm UTC
we could use CASE and REPLACE instead if you like ;)
Please try this
ROCKY, August 28, 2013 - 8:03 am UTC
Step 1:- Create a function like this
CREATE OR REPLACE function isnumber(a varchar2)
return varchar2 is
begin
-- Coded by ROCKY
for i in 1..length(a) loop
if not((ascii(substr(a,i,1))>=48) and
(ascii(substr(a,i,1))<=57)) then
return 'F';
end if;
end loop;
return 'T';
end isnumber;USAGE like this
select isnumber('ASDV2') from dual;Return value 'T' = is a number
Return value 'F' = is not a number
STEP 2: -select
regexp_like('039870931.00j','[^0-9.]','m') regexp,
DECODE(TRIM(TRANSLATE(replace('039870931.00h','.',''),'0123456789',' ')), NULL, '0','1') decod
from dual ;
August 28, 2013 - 7:22 pm UTC
why would rocky code something that doesn't work in general?
ops$tkyte%ORA11GR2> select isnumber( '1e100' ) isn, to_number( '1e100' ) n from dual;
ISN N
---------- ----------
F 1.000E+100
ops$tkyte%ORA11GR2> select isnumber( '1.2' ) isn, to_number( '1.2' ) n from dual;
ISN N
---------- ----------
F 1.2
rocky should use the code that was provided above.... it works for all Oracle numbers.
simple PL/SQL piece to do it
Waleed, September 15, 2016 - 9:07 am UTC
CREATE OR REPLACE FUNCTION isitreallyanumber (p_srting VARCHAR2)
RETURN VARCHAR2
IS
dummy NUMBER;
BEGIN
dummy := TO_NUMBER (NVL (p_srting, 'a null value!!')); -- null values are not a number, we examine only numbers here.
RETURN 'Y';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'N';
END;
--test
SELECT isitreallyanumber ('') FROM DUAL;
SELECT isitreallyanumber ('12aads') FROM DUAL;
SELECT isitreallyanumber ('4234') FROM DUAL;') FROM DUAL;