CODEWITHSUNDEEP
sqlregexoracle-databasereplace
Rasool Ghafari
Rasool Ghafari

Reputation: 4278

How to replace value in string except last occurrence of specific value in oracle

Assume that i have this column value:

new operational goods123 type 1 for main goods type 9

I want to get value of digit after last occurrence of type word with replace and using regular expression. In this case i want to find 9. I think that regular expression maybe something like this:

select REGEXP_REPLACE((SELECT REGEXP_REPLACE(' new operational goods123 type 1 for main goods type 2 ',
                                            '[^(type)[:digit:]]',
                                            ' ')
                        FROM dual),
                      '[^[:digit:]]')
  from dual;

but this query returns 12319.

Upvotes: 1

Views: 870

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627536

You may use

select REGEXP_REPLACE('new operational goods123 type 1 for main goods type 9',
                      '.*type\s*([0-9]+).*',
                      '\1',
                      1,
                      1,
                      'n'
                     ) as Result from dual

enter image description here

See the online demo

The pattern matches

  • .* - any 0+ chars as many as possible (including newlines as n modifier is used)
  • type - the last occurrence of type in string (due to the preceding greedy pattern)
  • \s* - 0+ whitespace chars
  • ([0-9]+) - 1 or more digits captured into Group 1
  • .* - the rest of the string.

The replacement contains a \1 placeholder referencing the value in Group 1.

The first 1 is the starting position for the search engine and the second 1 is the "the occurrence of the replace operation" (we need to replace only once).

Upvotes: 3

Gaj
Gaj

Reputation: 886

Try this

select substr('new operational goods123 type 1 for main goods type 9', instr('new operational goods123 type 1 for main goods type 9', 'type', -1)+ 5) from dual

Note: I have assumed there is a space after "type"

Upvotes: 0

Related Questions