How to Tell If a Value Is Not Numeric in Oracle

How can you tell if a value is not numeric in Oracle?

REGEXP_LIKE(column, '^[[:digit:]]+$')

returns TRUE if column holds only numeric characters

check if "it's a number" function in Oracle

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 > 0 
               THEN 'Number > 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.

How can I determine if a string is numeric in SQL?

You can use REGEXP_LIKE:

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '') 

Is Numeric in Oracle

You can select your data with a regexp_like :

SELECT *
FROM your_table t
WHERE REGEXP_LIKE (t.your_colonne, '^[0-9]+$');

Finding non-numeric values in varchar column

The precision means that you want at most allowed_precision digits in the number (strictly speaking, not counting leading zeros, but I'll ignore that). The scale means that at most allowed_scale can be after the decimal point.

This suggests a regular expression such as:

[-]?[0-9]{1,<before>}[.]?[0-9]{0,<after>}

You can construct the regular expression:

NOT REGEXP_LIKE(COLUMN_NAME,
                REPLACE(REPLACE('[-]?[0-9]{1,<before>}[.]?[0-9]{0,<after>}', '<before>', allowed_precision - allowed_scale
                               ), '<after>', allowed_scale)

Now, variable regular expressions are highly inefficient. You can do the logic using like and other functions as well. I think the conditions are:

(column_name not like '%.%.%' and
 column_name not like '_%-%' and
 translate(column_name, '0123456789-.x', 'x') is null and
 length(translate(column_name, '-.x', 'x') <= allowed_precision and
 length(translate(column_name, '-.x', 'x') >= 1 and
 instr(translate(column_name, '-.x', 'x'), '.') <= allowed_precision - allowed_scale
)

How to test column value is Numeric in Oracle

I think you just need else clauses:

select
 case when regexp_like(t2.flex_field1, '^-?[[:digit:],.]*$') then t2.flex_field21 else t1.flex_field21 end as field1, 
 case when regexp_like(t2.flex_field2, '^-?[[:digit:],.]*$') then t2.flex_field22 else t1.flex_field22 end as field2,
 case when regexp_like(t2.flex_field4, '^-?[[:digit:],.]*$') then t2.flex_field24 else t1.flex_field23 end as field3,
 case when regexp_like(t2.flex_field5, '^-?[[:digit:],.]*$') then t2.flex_field25 else t1.flex_field24 end as field4,
 case when regexp_like(t2.flex_field8, '^-?[[:digit:],.]*$') then t2.flex_field28 else t1.flex_field25 end as field5

EDIT:

Why are you doing this with a join?

update ao_invoice_detail 
    set flex_field21 = (case when regexp_like(flex_field1, '^-?[[:digit:],.]*$') then flex_field1 else flex_field21 end),
        flex_field22 = (case when regexp_like(flex_field2, '^-?[[:digit:],.]*$') then flex_field2 else flex_field22 end),
        flex_field23 = (case when regexp_like(flex_field4, '^-?[[:digit:],.]*$') then flex_field4 else flex_field23 end),
        flex_field24 = (case when regexp_like(flex_field5, '^-?[[:digit:],.]*$') then flex_field5 else flex_field24 end),
        flex_field25 = (case when regexp_like(flex_field8, '^-?[[:digit:],.]*$') then flex_field8 else flex_field25 end);

How can I know which values are numeric in oracle 9i

I manage to work around like this:

select my_column
from my_table
where my_column not like '%1%'
and my_column not like '%2%'
and my_column not like '%3%'
and my_column not like '%4%'
and my_column not like '%5%'
and my_column not like '%6%'
and my_column not like '%7%'
and my_column not like '%8%'
and my_column not like '%9%'
and my_column not like '%0%' 

Dirty, but it works. ;)

---

Related Topics