Re: Oracle equivalent of IsNumeric in SQLServer

c266366430_at_hotmail.com (Daniel Kwan) wrote in news:f724a6c4.0208290711.641f9a45_at_posting.google.com:

> In Transact-SQL (SQLServer 7/2000), there's a function called
> IsNumeric to determine whether an expression is a valid numeric type
> or not.
>
> For instance, "SELECT ISNUMERIC("No Number")" will return 0 (false),
> and "SELECT ISNUMERIC(123)" or "SELECT ISNUMERIC("123")" will return 1
> (true).
>
> Is there a similar function in Oracle SQL?

Not really a function just for that but the tools are there in Oracle SQL and you can always create your own function out of them if you want.

Using transalate you can strip out all valid numbers, if you are left with nothing then all you had were valid numbers. Here's what I mean:

SQL> create table t (string varchar2(20));

Table created.

SQL> insert into t values ('12345');

1 row created.

SQL> insert into t values ('a12345');

1 row created.

SQL> insert into t values ('12b345');

1 row created.

SQL> insert into t values ('12b345xx');

1 row created.

SQL> insert into t values ('987');

1 row created.

SQL> insert into t values ('987.01');

1 row created.

SQL> insert into t values ('1,987.01');

1 row created.

SQL> insert into t values ('987*');

1 row created.

SQL> select string,
  2 translate(string,'x0123456789','x') alphas   3 from t;

STRING ALPHAS

-------------------- ------
12345
a12345               a
12b345               b
12b345xx             bxx
987
987.01               .
1,987.01             ,.
987*                 *

8 rows selected.

Here anything that only contained valid numbers is now null. So you can wrap that in decode and return 0, 1 or anything you want.

SQL> select string,
  2 decode(translate(string,'x0123456789','x'),null,1,0) isnumeric   3 from t;

STRING ISNUMERIC

-------------------- ---------
12345                        1
a12345                       0
12b345                       0
12b345xx                     0
987                          1
987.01                       0
1,987.01                     0
987*                         0

8 rows selected.

Its also easily adaptable for special cases, for instance if you want to include 987.01 as a number then add '.' to the characters to be removed.

SQL> select string,
  2 decode(translate(string,'x.0123456789','x'),null,1,0) isnumeric   3 from t;

STRING ISNUMERIC

-------------------- ---------
12345                        1
a12345                       0
12b345                       0
12b345xx                     0
987                          1
987.01                       1
1,987.01                     0
987*                         0

8 rows selected.

Likewise for 1,987.01

SQL> select string,
  2 decode(translate(string,'x.,0123456789','x'),null,1,0) isnumeric   3 from t;

STRING ISNUMERIC

-------------------- ---------
12345                        1
a12345                       0
12b345                       0
12b345xx                     0
987                          1
987.01                       1
1,987.01                     1
987*                         0

Documentation is at:

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server. 920/a96540/functions150a.htm#79574

Hth

Martin Received on Fri Aug 30 2002 - 19:58:21 CDT