14

In a SQL query on Oracle 10g, I need to determine whether a string is numeric or not. How can I do this?

Sergey Glotov
  • 20,200
  • 11
  • 84
  • 98
AndrewL
  • 3,126
  • 5
  • 31
  • 33

7 Answers7

32

You can use REGEXP_LIKE:

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '')
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • I like this solution, but it doesn't match numbers that have multiple digits before the decimal point. e.g. (43, 43.6) – AndrewL Apr 14 '11 at 17:28
  • @Andy: Updated the post.. Pls chk. – Chandu Apr 14 '11 at 17:30
  • 4
    @cybernate: not i18n/l10n safe - commas v. periods. – Adam Musch Apr 14 '11 at 21:49
  • -1, 1.2E10, etc. A function with TO_NUMBER is probably the only safe way to do this. – Jon Heller Apr 14 '11 at 23:40
  • @jonearles: Can you please run this query and let me know what's the result? SELECT TO_NUMBER('344.8888O') FROM DUAL; – Chandu Apr 15 '11 at 01:34
  • @Cybernate: I meant a PL/SQL function that uses TO_NUMBER. As much as I hate adding PL/SQL functions to a query, I think in this case it's the only way to get the maximum functionality. – Jon Heller Apr 15 '11 at 02:42
  • @jonearles: Well I hate using exception to drive a function return value for something like number validation. – Chandu Apr 15 '11 at 02:45
  • @jonearles: TO_NUMBER is not safe as server can have another decimal symbol for NLS_NUMERIC_CHARACTERS than client's OS. – LoBo Mar 30 '12 at 15:19
  • @LoBo You'll be fine if you change the client's session parameters. (Functions use the session paramters, not the parameters used when they were created.) – Jon Heller Apr 02 '12 at 20:06
6

You ca try this:

SELECT LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) FROM DUAL

where string1 is what you're evaluating. It will return null if numeric. Look here for further clarification

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • 6
    Be aware that this considers a string like '123+45' or '123.45.67.89' to be a valid number. – Justin Cave Apr 14 '11 at 17:31
  • Thanks @Adrian , you gave me an idea on how to test if a string contains a number by combining translate and decode tegether – Ahmad Jan 29 '12 at 05:14
2

As pointed out by Tom Kyte in http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7466996200346537833, if you're using the built-in TO_NUMBER in a user defined function, you may need a bit of extra trickery to make it work.

FUNCTION is_number(x IN VARCHAR2)
RETURN NUMBER
IS
    PROCEDURE check_number (y IN NUMBER)
    IS
    BEGIN
        NULL;
    END;
BEGIN
    PRAGMA INLINE(check_number, 'No');
    check_number(TO_NUMBER(x);
    RETURN 1;
EXCEPTION
    WHEN INVALID_NUMBER
    THEN RETURN 0;
END is_number;

The problem is that the optimizing compiler may recognize that the result of the TO_NUMBER is not used anywhere and optimize it away.

Says Tom (his example was about dates rather then numbers):

the disabling of function inlining will make it do the call to check_date HAS to be made as a function call - making it so that the DATE has to be pushed onto the call stack. There is no chance for the optimizing compiler to remove the call to to_date in this case. If the call to to_date needed for the call to check_date fails for any reason, we know that the string input was not convertible by that date format.

SQB
  • 3,926
  • 2
  • 28
  • 49
2

I don't have access to a 10G instance for testing, but this works in 9i:


CREATE OR REPLACE FUNCTION is_numeric (p_val VARCHAR2)
   RETURN NUMBER
IS
v_val   NUMBER;
BEGIN
   BEGIN
      IF p_val IS NULL OR TRIM (p_val) = ''
      THEN
         RETURN 0;
      END IF;

      SELECT TO_NUMBER (p_val)
        INTO v_val
        FROM DUAL;

      RETURN 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 0;
   END;
END;


SELECT is_numeric ('333.5') is_numeric
  FROM DUAL;

I have assumed you want nulls/empties treated as FALSE.

Evan Haas
  • 2,524
  • 2
  • 22
  • 34
0

Here is a method to determine numeric that can be part of a simple query, without creating a function. Accounts for embedded spaces, +- not the first character, or a second decimal point.

var v_test varchar2(20);
EXEC :v_test := ' -24.9 ';

 select
 (case when trim(:v_test) is null then 'N' ELSE   -- only banks, or null
 (case when instr(trim(:v_test),'+',2,1) > 0 then 'N' ELSE  -- + sign not first char
 (case when instr(trim(:v_test),'-',2,1) > 0 then 'N' ELSE  -- - sign not first char
 (case when instr(trim(:v_test),' ',1,1) > 0 then 'N' ELSE  -- internal spaces
 (case when instr(trim(:v_test),'.',1,2) > 0 then 'N' ELSE  -- second decimal point
 (case when LENGTH(TRIM(TRANSLATE(:v_test, ' +-.0123456789',' '))) is not null then 'N' ELSE  -- only valid numeric charcters.
  'Y'
  END)END)END)END)END)END) as is_numeric
  from dual;
John Ryan
  • 11
-1

I found that the solution 

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) is null

allows embedded blanks ... it accepts "123 45 6789" which for my purpose is not a number.

Another level of trim/translate corrects this. The following will detect a string field containing consecutive digits with leading or trailing blanks such that to_number(trim(string1)) will not fail

LENGTH(TRIM(TRANSLATE(translate(trim(string1),' ','X'), '0123456789', ' '))) is null
Peter DeGregorio
  • 1,729
  • 1
  • 11
  • 5
  • That's because you included the space character at the beginning of the second parameter to TRANSLATE. If you remove the space before the +, is will not allow spaces in the strings. – TheChrisPratt Mar 04 '17 at 00:05
-1

For integers you can use the below. The first translate changes spaces to be a character and the second changes numbers to be spaces. The Trim will then return null if only numbers exist.

TRIM(TRANSLATE(TRANSLATE(TRIM('1 2 3d 4'), ' ','@'),'0123456789',' ')) is null
zlsmith86
  • 380
  • 4
  • 11