CODEWITHSUNDEEP
oracle-database
user3101337
user3101337

Reputation: 364

Oracle regex for all characters after a specific character

I need to get all characters after the last 'R' from a part number like this:

223A GHH R337716

So far I've got:

 REGEXP_SUBSTR(CUST_PART_NO,'R(.*)')

This returns R337716, but I don't want the 'R' and I'm not sure if it would work if there was more than one 'R' in the string.

Upvotes: 1

Views: 4746

Answers (2)

RayCW
RayCW

Reputation: 184

What about 

ltrim(REGEXP_SUBSTR(CUST_PART_NO,'R(.*)'), 'R')

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23381

Then you have to replace the string with groups of expressions like this:

regexp_replace( CUST_PART_NO, '(.*)(R)(.*)', '\3' )

See it here on sqlfiddle: http://sqlfiddle.com/#!4/3ec77/2

What I'm saying with this expression is:

  • get everything until R make it as a group 1 expression the first (.*)
  • get the R make it the second group (R)
  • get everything else and make it the third group (.*)

The parenthesis on a regular expression define groups of expressions.

Upvotes: 8

Related Questions