CODEWITHSUNDEEP
sqloracle-databasetype-conversion
Nandy
Nandy

Reputation: 23

ORACLE - TO_NUMBER Function

I have column TEXT_INT in my table and I would like convert to DECIMAL using TO_NUMBER Function. Unfortunately I am getting

INVALID NUMBER ORA-722 error.

I doubt it may have characters in it. So Issued below query but there is no alphabets in TEXT_INT.

SELECT * 
FROM NANTHA_TABLE 
WHERE UPPER(TEXT_INT) != LOWER(TEXT_INT);

Would you please provide an idea to resolve this issue or ways to finding wrong data?

Upvotes: 0

Views: 1620

Answers (3)

pOrinG
pOrinG

Reputation: 935

Please use below query to find records where TEXT_INT column has characters other than number. 

select  * from NANTHA_TABLE  where NOT REGEXP_LIKE(TEXT_INT,'^[0-9]+$')

In case you are expecting more string symbols in your column which should be considered as valid you can include them in the NOT REGEXP_LIKE condition so those columns also don't appear in the query.

eg. If expecting a '-' symbol in the start of the string for some values:

NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$') "

where

  • '^' Begining
  • '?' Zero or One occurance
  • '+' One of More occurance
  • '$' End of String

Please note i know that the decimals in my above query will be accepted even if counted twice. For that I propose a different solution to count the number of decimals in the string and throw error. If any1 can integrate it with the REGEXP you are most welcome. How to count number of decimals for check.

 LENGTH(COLUMN_NAME) - LENGTH(REPLACE(COLUMN_NAME,'.','')) > 1

To find more details about the REGEXP_LIKE please use the following link.

Upvotes: 0

CompEng
CompEng

Reputation: 7416

try this:

create function like this:

CREATE OR REPLACE function f_invalid_number(number_field in varchar2) return number as
  n_d number;
begin
  n_d := TO_number(number_field);
  return 0;
exception 
  when others then
    return 1;
end;
/

then you can check invalid data like this:

SELECT * 
FROM NANTHA_TABLE 
WHERE f_invalid_number(TEXT_INT) =1

Upvotes: 5

mehmet sahin
mehmet sahin

Reputation: 812

This query may help you.

select your_wrong_col ,
       case
          when trim(TRANSLATE(your_wrong_col ,'0123456789','')) is null
             then 'numeric'
             else 'not_numeric'
       end as your_wrong_col
from YOUR_TABLE
where trim(TRANSLATE(your_wrong_col ,'0123456789','')) is not null;

Finds non-numeric rows.

Upvotes: 0

Related Questions