[RESOLVED] Oracle Equivalent of IsNumeric

[brucevde]

I have this query I use against SQL Server that I need to execute against an Oracle database.

I can't find an equivalent Oracle function for IsNumeric.

SELECT  
     CASE WHEN IsNumeric(SubStr(PERSON.NAME_FULL_FORMATTED,1,1)) = 1 Then  
        PERSON.LASTNAME + ', ' + PERSON.FIRSTNAME
     ELSE
        PERSON.NAME_FULL_FORMATTED
     END

I use this web site for help on writing Oracle queries, maybe it is outdated.
http://www.techonthenet.com/oracle/f...ndex_alpha.php

[abhijit]

Hi bruce,
There is no easy way. You can use the translate function or make use of a regular expression to determine if it is numeric.

The last trick is to create a function that will give you the SQL Server equivalent of ISNUMERIC in oracle.

create or replace
  2  function isnumeric
  3  ( p_string in varchar2)
  4  return boolean
  5  as
  6      l_number number;
  7  begin
  8      l_number := p_string;
  9      return TRUE;
 10  exception
 11      when others then
 12          return FALSE;
 13  end;

[brucevde]

I cannot create a procedure/function in the database so I used the Translate function. Thanks.

SELECT CASE WHEN
  TRANSLATE(SUBSTR(FULL_ENCNTR_PERSON.NAME_FULL_FORMATTED,1,1),'0123456789',' ') IS NULL THEN
  FULL_ENCNTR_PERSON.LASTNAME||', '||FULL_ENCNTR_PERSON.FIRSTNAME
  ELSE FULL_ENCNTR_PERSON.NAME_FULL_FORMATTED END ClientName

[abhijit]

In any case, the regular expression is supported by 10G and above.

[leinad31]

bruce, zero length string is NULL but string full of spaces is not NULL. If version is 10g or higher then definitely use regex.

[brucevde]

They have Oracle 9.

zero length string is NULL but string full of spaces is not NULL.

?? I am not sure what you are trying to say. The statement I posted works fine for my needs.

[leinad31]

Translate will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

If SUBSTR(FULL_ENCNTR_PERSON.NAME_FULL_FORMATTED,1,1 is '0' then result of translate is a space instead of expected NULL, only the other numbers are replace with nullstring since you didn't provide matching characters in replacement_string (only '0' is paired with ' ').

If you will never encounter a leading zero in FULL_ENCNTR_PERSON.NAME_FULL_FORMATTED then yes your query will work but if there's data with leading '0' then your solution will fail.

[brucevde]

After more testing (i found 1 record with a leading 0 - thanks leinad31) and searching I ended up using the following

SELECT CASE WHEN 
  LENGTH(TRANSLATE(SUBSTR(TRIM(NAME_FULL_FORMATTED),1,1), '+-.0123456789', ' ')) IS NULL THEN
  TRIM(LASTNAME||', '||FIRSTNAME)
ELSE 
  TRIM(NAME_FULL_FORMATTED)
END

[leinad31]

If you want selected characters in your list to be replaced with nullstring and 3rd parameter is required by TRANSLATE then include a first char placeholder (not commonly used in data) to do a fake replace, such as "replace space with space".

[leinad31]

You can also try this and see if it is appropriate to your needs, just modify exception handling accordingly if you prefer to throw exceptions rather than an all or nothing approach. This also checks against specified precision and scale.

FUNCTION IS_NUMBER (p_value VARCHAR2, p_precision NUMBER DEFAULT NULL, p_scale NUMBER DEFAULT NULL)
      RETURN NUMBER
   AS
      func_ret    NUMBER;

      v_sql       VARCHAR2(4000) := 'SELECT CAST(:1 AS NUMBER <ps>) FROM DUAL';

      v_prevmod   VARCHAR2(100); -- V$SESSION.MODULE&#37;TYPE;
      v_prevact   VARCHAR2(100); -- V$SESSION.ACTION%TYPE;
      v_subprog   VARCHAR2(100) := 'IS_NUMBER';
   BEGIN
      DBMS_APPLICATION_INFO.Read_Module (v_prevmod, v_prevact);
      DBMS_APPLICATION_INFO.Set_Module  (PKG_NAME, v_subprog);

      BEGIN
         IF (p_precision IS NULL) AND (p_scale IS NULL) THEN
            func_ret := TO_NUMBER (p_value);
         ELSE
            IF (p_precision IS NULL) THEN
               v_sql := REPLACE (v_sql, '<ps>', '(*,' || p_scale || ')');
            ELSIF (p_scale IS NULL) THEN
               v_sql := REPLACE (v_sql, '<ps>', '(' || p_precision || ')');
            ELSE
               v_sql := REPLACE (v_sql, '<ps>', '(' || p_precision || ',' || p_scale || ')');
            END IF;

            EXECUTE IMMEDIATE v_sql INTO func_ret USING p_value;
         END IF;
      EXCEPTION
      WHEN OTHERS THEN
         func_ret := NULL;
      END;