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  gt 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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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   gt 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

User · Answer

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   gt  0    It won t return any rows that Oracle sees as an invalid number   Cheers

User · Answer

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

User · Answer

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

User · Answer

You can use this example  SELECT NVL  SELECT 1 FROM  DUAL WHERE   REGEXP LIKE   VALOR      digit         0  FROM DUAL

User · Answer

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   gt   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

User · Answer

if condition is null then it is number  IF rtrim P COD LEGACY   0123456789   IS NULL  THEN                 return 1            ELSE                 return 0            END IF

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0                 THEN  Number  gt  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

User · Answer

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

User · Answer

This is a potential duplicate of Finding rows that don  39 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

User · Answer

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  gt  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

[oracle] check if "it's a number" function in Oracle

Examples related to oracle

Examples related to plsql

Examples related to oracle10g