0

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.

mrcrag
  • 310
  • 3
  • 16
  • Do you have values with exactly 3 or exactly 10 digts after the decimal point? If they're floats (numbers) why are you trying to filter them using string functions? – Alex Poole Dec 13 '18 at 13:44
  • Are you sure you're actually even matching 0.25? I can see 1.25 working, but the implicit `to_char(c)` you're doing won't have a zero before the decimal point? – Alex Poole Dec 13 '18 at 14:00
  • Yes, there are entries with 1-15 decimal places. I think your trunc() will work. Trying it now, thanks. – mrcrag Dec 13 '18 at 14:03

1 Answers1

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318