[RESOLVED] Oracle Equivalent of IsNumeric

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.

Code:

---

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

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.

sql Code:

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;

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

Code:

---

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

---

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

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

They have Oracle 9.

Quote:

---

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.

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.

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

Code:

---

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

---

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

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.

Code:

---

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;

---