Oracle regexp_like failing on FLOAT from view

Question

I'm trying to use regexp_like to find and remove overly-precise floating point numbers.

select c from t order by c asc;

returns many results like this: 0.0000000012345678

Using regexp_like I can get results for two decimal places (0.25):

select * from t where REGEXP_LIKE(c,'^\d+\.\d{2}');

However, when I try anything more than two places, I get no results:

select * from t where REGEXP_LIKE(c,'^\d+\.\d{3}');
...
select * from t where REGEXP_LIKE(c,'^\d+\.\d{10}');

The only add'l info is that I'm selecting against a view of a second view and the column I'm searching (c, above) is designated as a FLOAT.

Answer 1

You can treat them as numbers. You can truncate the value to a fixed number of decimal places:

The TRUNC (number) function returns n1 truncated to n2 decimal places.

and then see if it matches. For example, to find any values with more than 2 significant digits after the decimal point:

 select * from t where c != trunc(c, 2);

or to find those with more than 10 significant digits:

 select * from t where c != trunc(c, 10);

I've used != rather than > in case you have negative values.

You can also use that as a filter in a delete/update, or as the set part of an update if you want to reduce the precision - though in that case you might want to use round() instead fo trunc().

---

When you use regexp_like you're doing an implicit conversion of your float value to a string, and as the docs for to_char() note:

If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

which means that 0.25 becomes the string '.25', with no leading zero; which doesn't match even your first pattern.

You can allow for that leading zero not being there by using * instead of +, e.g. to find values with at least 10 significant digits after the decimal point:

select * from t where REGEXP_LIKE(c,'^\d*\.\d{10}');

or with exactly 10:

select * from t where REGEXP_LIKE(c,'^\d*\.\d{10}$');

etc.; but it seems simpler to treat them just as numbers rather than as strings.