ODIExperts.com

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'
       end
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!


10 Comments

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

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

    CREATE OR REPLACE FUNCTION is_number(p_value VARCHAR2)
    RETURN NUMBER
    AS
    v_num NUMBER;
    BEGIN
    v_num:=TO_NUMBER(p_value);
    RETURN 1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
    END;
    /

    • 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 ?

  3. This is other one variant:

    SELECT
    CASE
    WHEN REGEXP_LIKE(‘1.2′,’^[0-9.,]*$’)
    THEN ‘numeric’
    ELSE ‘alpha’
    END
    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:

      SELECT
      CASE
      WHEN REGEXP_LIKE(‘1.5.2′,’^[0-9.,]*$’)
      THEN ‘numeric’
      ELSE ‘alpha’
      END
      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!

    Cheers,

    Cezar

  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.

    Cheers,
    Andy

Leave a Reply

Required fields are marked *.