Q:

oracle regular expression with number matching

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

A:

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

A:

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 string

