How can I determine if a string is numeric in SQL?

In a SQL query on Oracle 10g, I need to determine whether a string is 开发者_如何学Pythonnumeric or not. How can I do this?

---

You can use REGEXP_LIKE:

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '') 

---

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

---

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.

---

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.

---

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;

---

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

---

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