Invalid number error when working with DFF attributes..

Invalid number error when working with DFF attributes when using to_number:

When we are working with DFFs, segments can be configured to store numbers but due to user wrong input or some misconfiguration some rows might end with having string with numbers and characters. When we use that attribute in a query and try to compare it to a number or try to convert to a number, the system will throw error. To avoid the error, we can use the following condition in the query:

select * from po_headers_all
where org_id=101
and nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)=0;


to find invalid rows you can use below.

nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)>0;