I have a table which contains a column 'col1' having values like
0.23
0.0123
90.013
23.31
0.87
100.013
How to get the records whole values matching with any number followed by 0.01 followed by any number in Oracle? I.e.
0.0123
90.013
100.013
If you want a number containing 0.01 (zero units, zero tenths and one hundredth) then you do not need regular expressions:
SELECT *
FROM your_table
WHERE INSTR( TO_CHAR( col1, '99999999999999990.00' ), '0.01' ) > 0
If you want a number containing .01 (zero tenths and one hundredth) then:
SELECT *
FROM your_table
WHERE INSTR( TO_CHAR( col1, '99999999999999990.00' ), '.01' ) > 0
You may need the following, assuming you only have positive numbers:
select *
from (
select '0.23' as col1 from dual union all
select '0.0123' from dual union all
select '90.013' from dual union all
select '23.31' from dual union all
select '0.87' from dual union all
select '100.013' from dual
)
where regexp_like(col1, '^[0-9]+\.01[0-9]*$')
How it works:
^: the beginning of the string[0-9]+: one or more occurrences of a
digit\.01: exactly .01[0-9]*: zero or more occurrences of a digit$: the end of the stringUsing Number Functions
Principle: reduce number to the whole number and whole number with first two decimals and perform a comparison.
SCOTT@dev> --.01 match
32 SELECT
33 val,
34 floor(val) whole_num,
35 trunc(
36 val,
37 2
38 ) whole_num_with_dec
39 FROM
40 smple
41 WHERE
42 trunc(
43 val,
44 2
45 ) - floor(val) =0.01;
val whole_num whole_num_with_dec
0.0123 0 0.01
90.013 90 90.01
100.013 100 100.01
for the 0.01 match, I just shift the decimal to the left one digit and perform the same evaluation:
32 SELECT
33 val,
34 floor(val / 10) mod_whole_num,
35 trunc(
36 val / 10,
37 3
38 ) mod_whole_num_with_dec
39 FROM
40 smple
41 WHERE
42 trunc(
43 val / 10,
44 3
45* ) - floor(val / 10) =0.001;
val mod_whole_num whole_num_with_dec
0.0123 0 0.001
90.013 9 9.001
100.013 10 10.001
