How can you tell if a value is not numeric in Oracle?

Question

I have the following code that returns an error message if my value is invalid. I would like to give the same error message if the value given is not numeric.

IF(option_id = 0021) THEN 
      IF((value<10000) or (value>7200000) or /* Numeric Check */)THEN
          ip_msg(6214,option_name);  -- Error Message
          return;
      END IF;
END IF;

In SQL Server, I simply used ISNUMERIC(). I would like to do something similar in Oracle. Such as,

IF((!ISNUMERIC(value)) or (value<10000) or (value>7200000))
    THEN ...

score 80 · Accepted Answer · answered Mar 02 '15 at 22:08

80

REGEXP_LIKE(column, '^[[:digit:]]+$')

returns TRUE if column holds only numeric characters

answered Mar 02 '15 at 22:08

Rob van Laarhoven

8,737
2
31
49

7

Of course, not all numbers would be composed entirely of numeric characters. "1.2" is numeric but it contains a period (or a comma depending on your NLS settings). "-100" is numeric but it contains a minus sign. "1.2.3" is not numeric though it is composed entirely of digits and periods. The `regexp_like` may be sufficient if you're really looking to see whether the column holds positive integers with no formatting. – Justin Cave Mar 02 '15 at 22:21
4

`123e-5` is also a number. – Shannon Severance Mar 02 '15 at 22:26
4

This regex would cover only positive integer values! – Wernfried Domscheit Mar 03 '15 at 07:15
Use it as: `SELECT column_with_int_and_string FROM MyTable WHERE NOT REGEXP_LIKE(column_with_int_and_string, ‘^[[:digit:]]+$’);` – dkb Mar 12 '20 at 12:21
This regex can be expanded as necessary if more than just numeric digits is required, but I think this answer satisfies the OP and is a great start for anyone with a similar issue. Here's an example to get people going with a little more complex regex that should cover all integers (untested): `'^[+-]?[[:digit:]]+$'` – Jason Dec 09 '21 at 15:23
1

Use this for decimal numbers: REGEXP_LIKE(COLUMN, '^-?[[:digit:].]*$') – Amjad Abu Saa Apr 07 '22 at 09:13

Lukasz Szozda · Answer 2 · 2018-10-23T14:59:54.870

45

From Oracle DB 12c Release 2 you could use VALIDATE_CONVERSION function:

VALIDATE_CONVERSION determines whether expr can be converted to the specified data type. If expr can be successfully converted, then this function returns 1; otherwise, this function returns 0. If expr evaluates to null, then this function returns 1. If an error occurs while evaluating expr, then this function returns the error.

 IF (VALIDATE_CONVERSION(value AS NUMBER) = 1) THEN
     ...
 END IF;

db<>fiddle demo

edited Oct 23 '18 at 14:59

answered Aug 25 '17 at 17:44

Lukasz Szozda

162,964
23
234
275

1

@MarvinM Great to hear that. Please also check [DEFAULT ... ON CONVERSION ERROR](https://stackoverflow.com/a/45886745/5070879) – Lukasz Szozda Aug 14 '19 at 16:39
I can't understand why this function return 1 if the value is null. It should be 0 – Salman Sep 20 '20 at 10:36
@Salman It was defined that way probably because `SELECT CAST(null AS ) c FROM dual;` is a valid cast. – Lukasz Szozda Sep 20 '20 at 18:27
`VALIDATE_CONVERSION('' AS NUMBER)` - it says `1` - same goes for `IS_NUMERIC('')`... – Marco Aurelio Fernandez Reyes Apr 13 '23 at 15:06
If the string to check does not have decimals, this works: `CASE WHEN TO_NUMBER('') IS NOT NULL THEN 1 ELSE 0 END` - why "`NULL`" or an empty string is valid/1? - doesn't make sense to me, tbh... – Marco Aurelio Fernandez Reyes Apr 13 '23 at 15:13
@MarcoAurelioFernandezReyes `''` <=> NULL in Oracle and NULL is perfectly valid value for number. `SELECT TO_NUMBER(NULL)`. If you create table `CREATE TABLE t(i INT)` you could insert `INSERT INTO t VALUES (NULL)` without any issue and the data type of column is still number. – Lukasz Szozda Apr 13 '23 at 16:10

Justin Cave · Answer 3 · 2019-06-18T15:45:40.737

19

There is no built-in function. You could write one

CREATE FUNCTION is_numeric( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 1;
EXCEPTION
  WHEN value_error
  THEN
    RETURN 0;
END;

and/or

CREATE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN l_num;
EXCEPTION
  WHEN value_error
  THEN
    RETURN NULL;
END;

You can then do

IF( is_numeric( str ) = 1 AND 
    my_to_number( str ) >= 1000 AND
    my_to_number( str ) <= 7000 )

If you happen to be using Oracle 12.2 or later, there are enhancements to the to_number function that you could leverage

IF( to_number( str default null on conversion error ) >= 1000 AND
    to_number( str default null on conversion error ) <= 7000 )

edited Jun 18 '19 at 15:45

answered Mar 02 '15 at 21:32

Justin Cave

227,342
24
367
384

1

I think you want `is_numeric( str ) = 0` above if I understand the OP correctly (equivalent of `!ISNUMERIC(value)`). – David Faber Mar 03 '15 at 12:47
1

@DavidFaber - I assume that is a typo. If a string is not numeric, it wouldn't make sense to then compare it against numeric values. – Justin Cave Mar 03 '15 at 20:23
1

Oh I see, you're using AND where the OP has OR. Mea culpa. – David Faber Mar 03 '15 at 21:00

score 12 · Answer 4 · answered Sep 11 '18 at 12:41

12

The best answer I found on internet:

SELECT case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
       end
FROM tab1;

answered Sep 11 '18 at 12:41

TechDo

18,398
3
51
64

1

This is not suitable in scenarios where you are trying to prevent spaces before, after, or even between the digits. – JamesHough Mar 28 '19 at 10:20
look at this https://www.techonthenet.com/oracle/questions/isnumeric.php – Udara Kasun Dec 10 '20 at 08:58

score 6 · Answer 5 · answered Mar 03 '15 at 12:47

You can use the following regular expression which will match integers (e.g., 123), floating-point numbers (12.3), and numbers with exponents (1.2e3):

^-?\d*\.?\d+([eE]-?\d+)?$

If you want to accept + signs as well as - signs (as Oracle does with TO_NUMBER()), you can change each occurrence of - above to [+-]. So you might rewrite your block of code above as follows:

IF (option_id = 0021) THEN 
    IF NOT REGEXP_LIKE(value, '^[+-]?\d*\.?\d+([eE][+-]?\d+)?$') OR TO_NUMBER(value) < 10000 OR TO_NUMBER(value) > 7200000 THEN
        ip_msg(6214,option_name);
        RETURN;
    END IF;
END IF;

I am not altogether certain that would handle all values so you may want to add an EXCEPTION block or write a custom to_number() function as @JustinCave suggests.

score 1 · Answer 6 · answered Apr 11 '21 at 18:11

1

This regular expression will match numbers like 5 , -5, +5, 5.44, 3.45e-3

REGEXP_LIKE('54.55e33', '^[+-]?\d+([.]\d+)?(e[+-]?\d+)?$')

answered Apr 11 '21 at 18:11

Yasser Helmy

19
3

score 0 · Answer 7 · answered Sep 28 '20 at 12:20

0

CREATE OR REPLACE FUNCTION IS_NUMERIC(P_INPUT IN VARCHAR2) RETURN INTEGER IS
  RESULT INTEGER;
  NUM NUMBER ;
BEGIN
  NUM:=TO_NUMBER(P_INPUT);
  RETURN 1;
EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END IS_NUMERIC;
/

answered Sep 28 '20 at 12:20

harun ugur

1,718
18
18

score -1 · Answer 8 · answered Feb 15 '21 at 16:20

-1

SELECT DECODE(REGEXP_COUNT(:value,'\d'),LENGTH(:value),'Y','N') AS is_numeric FROM dual;

There are many ways but this one works perfect for me.

answered Feb 15 '21 at 16:20

KOBER

59
1
6

How can you tell if a value is not numeric in Oracle?

8 Answers8

Linked

Related