CODEWITHSUNDEEP
sqloracle-database
Robin clave
Robin clave

Reputation: 638

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

Upvotes: 0

Views: 183

Answers (3)

Patrick Bacon
Patrick Bacon

Reputation: 4660

Using 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

Upvotes: 0

MT0
MT0

Reputation: 168681

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

Upvotes: 3

Aleksej
Aleksej

Reputation: 22969

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

Upvotes: 1

Related Questions