check if "it's a number" function in Oracle

I'm trying to check if a value from a column in an oracle (10g) query is a number开发者_C百科 in order to compare it. Something like:

select case when ( is_number(myTable.id) and (myTable.id >0) ) 
            then 'Is a number greater than 0' 
            else 'it is not a number' 
       end as valuetype  
  from table myTable

Any ideas on how to check that?

---

One additional idea, mentioned here is to use a regular expression to check:

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^[[:digit:]]+$');

The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.

---

Assuming that the ID column in myTable is not declared as a NUMBER (which seems like an odd choice and likely to be problematic), you can write a function that tries to convert the (presumably VARCHAR2) ID to a number, catches the exception, and returns a 'Y' or an 'N'. Something like

CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 )
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 'Y';
EXCEPTION
  WHEN value_error THEN
    RETURN 'N';
END is_number;

You can then embed that call in a query, i.e.

SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0 
               THEN 'Number > 0'
             ELSE 'Something else'
         END) some_alias
  FROM myTable

Note that although PL/SQL has a boolean data type, SQL does not. So while you can declare a function that returns a boolean, you cannot use such a function in a SQL query.

---

Saish's answer using REGEXP_LIKE is the right idea but does not support floating numbers. This one will ...

Return values that are numeric

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

Return values not numeric

SELECT  foo 
FROM    bar
WHERE   NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

You can test your regular expressions themselves till your heart is content at http://regexpal.com/ (but make sure you select the checkbox match at line breaks for this one).

---

This is a potential duplicate of Finding rows that don't contain numeric data in Oracle. Also see: How can I determine if a string is numeric in SQL?.

Here's a solution based on Michael Durrant's that works for integers.

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'0123456789',' ')), NULL, 'number','contains char') = 'number'

Adrian Carneiro posted a solution that works for decimals and others. However, as Justin Cave pointed out, this will incorrectly classify strings like '123.45.23.234' or '131+234'.

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'+-.0123456789',' ')), NULL, 'number','contains char') = 'number'

If you need a solution without PL/SQL or REGEXP_LIKE, this may help.

---

You can use the regular expression function 'regexp_like' in ORACLE (10g)as below:

select case
       when regexp_like(myTable.id, '[[:digit:]]') then
        case
       when myTable.id > 0 then
        'Is a number greater than 0'
       else
        'Is a number less than or equal to 0'
     end else 'it is not a number' end as valuetype
from table myTable

---

I'm against using when others so I would use (returning an "boolean integer" due to SQL not suppporting booleans)

create or replace function is_number(param in varchar2) return integer
 is
   ret number;
 begin
    ret := to_number(param);
    return 1; --true
 exception
    when invalid_number then return 0;
 end;

In the SQL call you would use something like

select case when ( is_number(myTable.id)=1 and (myTable.id >'0') ) 
            then 'Is a number greater than 0' 
            else 'it is not a number or is not greater than 0' 
       end as valuetype  
  from table myTable

---

This is my query to find all those that are NOT number :

Select myVarcharField
From myTable
where not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\.\d+)?$', '')
and not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\,\d+)?$', '');

In my field I've . and , decimal numbers sadly so had to take that into account, else you only need one of the restriction.

---

How is the column defined? If its a varchar field, then its not a number (or stored as one). Oracle may be able to do the conversion for you (eg, select * from someTable where charField = 0), but it will only return rows where the conversion holds true and is possible. This is also far from ideal situation performance wise.

So, if you want to do number comparisons and treat this column as a number, perhaps it should be defined as a number?

That said, here's what you might do:

create or replace function myToNumber(i_val in varchar2) return number is
 v_num number;
begin
 begin
   select to_number(i_val) into v_num from dual;
 exception
   when invalid_number then
   return null;
 end;
 return v_num;
end;

You might also include the other parameters that the regular to_number has. Use as so:

select * from someTable where myToNumber(someCharField) > 0;

It won't return any rows that Oracle sees as an invalid number.

Cheers.

---

CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END;
END is_number;

Please note that it won't consider 45e4 as a number, But you can always change regex to accomplish the opposite.

---

@JustinCave - The "when value_error" replacement for "when others" is a nice refinement to your approach above. This slight additional tweak, while conceptually the same, removes the requirement for the definition of and consequent memory allocation to your l_num variable:

function validNumber(vSomeValue IN varchar2)
     return varchar2 DETERMINISTIC PARALLEL_ENABLE
     is
begin
  return case when abs(vSomeValue) >= 0 then 'T' end;
exception
  when value_error then
    return 'F';
end;

Just a note also to anyone preferring to emulate Oracle number format logic using the "riskier" REGEXP approach, please don't forget to consider NLS_NUMERIC_CHARACTERS and NLS_TERRITORY.

---

well, you could create the is_number function to call so your code works.

create or replace function is_number(param varchar2) return boolean
 as
   ret number;
 begin
    ret := to_number(param);
    return true;
 exception
    when others then return false;
 end;

EDIT: Please defer to Justin's answer. Forgot that little detail for a pure SQL call....

---

You can use this example

SELECT NVL((SELECT 1 FROM  DUAL WHERE   REGEXP_LIKE (:VALOR,'^[[:digit:]]+$')),0) FROM DUAL;

---

Function for mobile number of length 10 digits and starting from 9,8,7 using regexp

create or replace FUNCTION VALIDATE_MOBILE_NUMBER
(   
   "MOBILE_NUMBER" IN varchar2
)
RETURN varchar2
IS
  v_result varchar2(10);

BEGIN
    CASE
    WHEN length(MOBILE_NUMBER) = 10 
    AND MOBILE_NUMBER IS NOT NULL
    AND REGEXP_LIKE(MOBILE_NUMBER, '^[0-9]+$')
    AND MOBILE_NUMBER Like '9%' OR MOBILE_NUMBER Like '8%' OR MOBILE_NUMBER Like '7%'
    then 
    v_result := 'valid';
    RETURN v_result;
      else 
      v_result := 'invalid';
       RETURN v_result;
       end case;
    END;

---

Note that regexp or function approaches are several times slower than plain sql condition.

So some heuristic workarounds with limited applicability make sence for huge scans.

There is a solution for cases when you know for sure that non-numeric values would contain some alphabetic letters:

select case when upper(dummy)=lower(dummy) then '~numeric' else '~alpabetic' end from dual

And if you know some letter would be always present in non-numeric cases:

select case when instr(dummy, 'X')>0 then '~alpabetic' else '~numeric' end from dual

When numeric cases would always contain zero:

select case when instr(dummy, '0')=0 then '~alpabetic' else '~numeric' end from dual

---

if condition is null then it is number

IF(rtrim(P_COD_LEGACY, '0123456789') IS NULL) THEN
                return 1;
          ELSE
                return 0;
          END IF;

---

Here's a simple method which :

• does not rely on TRIM
• does not rely on REGEXP
• allows to specify decimal and/or thousands separators ("." and "," in my example)
• works very nicely on Oracle versions as ancient as 8i (personally tested on 8.1.7.4.0; yes, you read that right)

SELECT
    TEST_TABLE.*,

    CASE WHEN
        TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a.,0123456789', 'a') IS NULL
    THEN 'Y'
    ELSE 'N'
    END
    AS IS_NUMERIC

FROM
    (
    -- DUMMY TEST TABLE
        (SELECT '1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '1,000.00' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz 123' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '.,' AS TEST_COLUMN FROM DUAL)
    ) TEST_TABLE

Result:

TEST_COLUMN IS_NUMERIC
----------- ----------
.,          Y
1           Y
1,000.00    Y
xyz 123     N
xyz1        N

5 rows selected.

Granted this might not be the most powerful method of all; for example ".," is falsely identified as a numeric. However it is quite simple and fast and it might very well do the job, depending on the actual data values that need to be processed.

For integers, we can simplify the Translate operation as follows :

TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a0123456789', 'a') IS NULL

How it works

From the above, note the Translate function's syntax is TRANSLATE(string, from_string, to_string). Now the Translate function cannot accept NULL as the to_string argument. So by specifying 'a0123456789' as the from_string and 'a' as the to_string, two things happen:

• character a is left alone;
• numbers 0 to 9 are replaced with nothing since no replacement is specified for them in the to_string.

In effect the numbers are discarded. If the result of that operation is NULL it means it was purely numbers to begin with.