check if “it's a number” function in Oracle

Question

I'm trying to check if a value from a column in an oracle (10g) query is a number 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?

Answer 1

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;

Answer 2

@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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

if condition is null then it is number

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