Warning: Illegal string offset 'ssb_og_tags' in /home/content/10/4632510/html/wp-content/plugins/simple-social-buttons/simple-social-buttons.php on line 1466


The blog for Oracle Data Integrator ( ODI )

IS_NUMBER (and IS_CHAR) at Oracle? A workaround…

Hi Friends,

Right now I’m in São Paulo airport waiting for a flying that is very delayed! With some time free, let me write about something that I want to publish there is a long time….

Surprise! Isn’t an Oracle Data Integrator issue! hehehehehehe……

I really like to work with ORACLE RDBMS but doesn’t exist a “Is Number” function is, in my opnion, a lack of funcionality that I can’t understand.

Every time that is necessary to check if the returned value from a column (or a substring from it ) is number, I created a database (PL/SQL) function that return true or false and then uses it in the query.

In this way works, but if I want to do the same in a Microsoft SQL Server, there is a “native” function to do it.

The big problem is that, sometimes, you don’t have an user that can create PL/SQL procedures and, then, you can’t do this type of validation.

For solve this, I created the following technique:

A single SQL query that can return if a column is number or alphanumeric

select case when trim(TRANSLATE('1234f','0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
from dual

Where ‘1234f’ should be substituted by the column that you wish to validate.

The principle is very simple, if  the numbers from a string are removed and the result is different of null then the string isn’t numeric. The Trim funcion eliminates any remained space.

Plus, it is pretty usefull when there some non-visible ASCII char  in a column.

Well, I couldn’t let to show a use of this in ODI.

When there is a text file to be loaded and it is necessary to check (CKM) if the columns are with the right data (thru ODI constraint) this single query can send to the error table (E$) any record that has the wrong data.

See you around!


  1. Thanks, This piece of code was very helpful.

  2. I suggest using the following function that returns 1 or 0:

    v_num NUMBER;
    RETURN 1;
    RETURN 0;

    • Hi Zahar,
      Suppose I have a string “MTS_06_20161213165138.tag” and I want to check if each character in that string is a number or a character.

      If I am using your function I wont be able to send the numerics to that function as it is accepting only of type VARCHAR2. How do I solve it ?

      • Sinha,

        Oracle doesn’t seem to mind this to much and should be able to convert to varchar2. My question for you though it, why would you need to run an IS_NUMERIC on a number field in Oracle. If the value is stored as a number, it is numeric.

        The above function is to test if a VARCHAR2 value is numbers like ‘1234’ yet being stored as VARCHAR2.


        • Hi Jonathan,

          It’s very useful when loading file to tables, you need to create a ODI constraint and move all records with this kind of problem to an E$ table without stop the entire process…

          Of course that is no reason to use it when get a value from an “already” numeric column.

          Do you think it make any sense?

        • Because in real world ETL, numeric data comes through dirty and in varchar fields. You need a test to see if it’s clean numeric data.

  3. This is other one variant:

    WHEN REGEXP_LIKE(‘1.2′,’^[0-9.,]*$’)
    THEN ‘numeric’
    ELSE ‘alpha’
    FROM dual;

    I like it more, becouse it is easier to read for me. But this is deal of taste :).

    • Hi Pavlo,

      the way you do is perfect!!! Congrats!

    • Unfortunately, this function fails when a “numeric” string contains two or more periods:

      WHEN REGEXP_LIKE(‘1.5.2′,’^[0-9.,]*$’)
      THEN ‘numeric’
      ELSE ‘alpha’
      FROM dual

      –this also returns ‘numeric’ though it is not a number

    • it also fails for negative numbers

    • If you insist on using a plain SQL (SELECT), you can add more filters in the CASE function:

      SELECT CASE WHEN INSTR(‘-1.-2′,’-‘)>1 THEN ‘alpha’ –if minus is not the very first symbol then ‘alpha’
      WHEN ‘-1.-2’ LIKE ‘%.%.%’ THEN ‘alpha’ –if more than 1 period then ‘alpha’
      WHEN ‘-1.-2’ LIKE ‘-%-%’ THEN ‘alpha’ –if more than one minus then ‘alpha’
      WHEN REGEXP_LIKE(‘-1.-2′,’^[-0-9.,]*$’) THEN ‘numeric’ –if all symbols are digits, a period, or a minus then ‘numeric’
      ELSE ‘alpha’
      END is_numeric
      FROM dual

  4. Hi Andy,

    Thank you very much for your comments.

    You are right. The idea is that you can add any character and make the “function” as useful as you need!



  5. Hi,

    The function as listed will only work for integers! e.g. “8.85” will show up as alphanumeric.

    You’ll need to include the decimal point (whichever character used in the national format) in the translation argument to verify input including decimal places.

    Excellent blog Cezar, keep up the good work.


Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.