CODEWITHSUNDEEP
regexoracle11g
vicky
vicky

Reputation: 21

how to get decimal values and special character using regex in oracle

in my table has values like below and texts are in front and back text (not able to get value using substring) decimal is not only like below.How to get these values from the specific columns

2.0GB/3.GB(not only GB  etc like many)
2.0/3.0 mp
2.0 GB/ 3.GB
2.0 /3.0 mp
2.0
2.0 mp
1

i used below query to get it is not work out for me . below query are

REGEXP_SUBSTR(column, '\d.*)

Upvotes: 1

Views: 3577

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626802

Use

REGEXP_SUBSTR(REGEXP_REPLACE(REGEXP_REPLACE(s, '^.*,', ''),'\[[0-9]+]|[0-9]+\s*-\s*[0-9]+|[a-zA-Z][0-9]+', ''), '[0-9][.0-9]*\s*[a-zA-Z]*([-/]\s*[.?0-9][.0-9]*\s*[a-zA-Z]*)?')

The first step is removing all up to the last comma:

REGEXP_REPLACE(s, '^.*,', '')

The second step is removing digit(s) inside square brackets, letters with digits after them and hyphen-separated ranges:

REGEXP_REPLACE(s,'\[[0-9]+]|[0-9]+\s*-\s*[0-9]+|[a-zA-Z][0-9]+', '')

Here, the pattern removes substrings matching:

  • \[ - a [
  • [0-9]+ - 1+ digits
  • ] - a ]
  • | - or
  • [0-9]+\s*-\s*[0-9]+ - 1+ digits ([0-9]+), a - enclosed with 0+ whitespaces and again 1+ digits
  • | - or
  • [a-zA-Z][0-9]+ - a letter with 1+ digits after it.

The third step is to extract the matches you need:

  • [0-9][.0-9]* - a digit followed with 0+ digits or . symbols
  • \s* - 0+ whitespaces
  • [a-zA-Z]* - 0+ letters
  • ([-/]\s*[.?0-9][.0-9]*\s*[a-zA-Z]*)? - an optional sequence of:
    • [-/] - a - or /
    • \s* - 0+ whitespaces
    • [.?0-9] - ., ? or digits
    • [.0-9]*\s*[a-zA-Z]* - see above

See the online demo:

with test(s) as (
    select 'text [1234] 1gb' from dual union all
    select '(some text)800 - 784(some text) 2 Gb' from dual union all
    select '2.0GB/3.GB' from dual union all
    select '2.0/3.0 mp' from dual union all
    select '2.0 /3.0 mp' from dual union all
    select '2.0' from dual union all
    select '2.0 mp' from dual union all
    select '1' from dual union all
    select '2.0 GB/ 3.GB' from dual
)
select s, 
       REGEXP_SUBSTR(REGEXP_REPLACE(REGEXP_REPLACE(s, '^.*,', ''),'\[[0-9]+]|[0-9]+\s*-\s*[0-9]+|[a-zA-Z][0-9]+', ''), '[0-9][.0-9]*\s*[a-zA-Z]*([-/]\s*[.?0-9][.0-9]*\s*[a-zA-Z]*)?')
from test

Output:

1   abc1 1gb,tb1 1 gb,eard1 2 gb,help2 12 gb    12 gb
2   text [1234] 1gb                             1gb
3   (some text)800 - 784(some text) 2 Gb        2 Gb
4   2.0GB/3.GB                                  2.0GB/3.GB
5   2.0/3.0 mp                                  2.0/3.0 mp
6   2.0 /3.0 mp                                 2.0 /3.0 mp
7   2.0                                         2.0
8   2.0 mp                                      2.0 mp
9   1                                           1
10  2.0 GB/ 3.GB                                2.0 GB/ 3.GB

Upvotes: 1

Related Questions