CODEWITHSUNDEEP
regexoracle-databasesubstr
Jochen Birkle
Jochen Birkle

Reputation: 335

Oracle Substring after specific character

I already found out I need to use substr/instr or regex but with reading the documentation about those, I cant get it done... I am here on Oracle 11.2.

So here is what I have. A list of Strings like:

743H5-34L-56
123HD34-7L
12HSS-34R
23Z67-4R-C23

What I need is the number (length 1 or 2) after the first '-' until there comes a 'L' or 'R'.

Has anybody some advice?

Upvotes: 2

Views: 10163

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30775

Another version (without fancy lookarounds :-) :

with v_data as (
  select '743H5-34L-56' val from dual
  union all
  select '123HD34-7L' val from dual
  union all 
  select '12HSS-34R' val from dual
  union all
  select '23Z67-4R-C23' val from dual
)
select 
  val, 
  regexp_replace(val, '^[^-]+-(\d+)[LR].*', '\1') 
from v_data

It matches

  • the beginning of the string "^"
  • one or more characters that are not a '-' "[^-]+"
  • followed by a '-' "-"
  • followed by one ore more digits (capturing them in a group) "(\d+)"
  • followed by 'L' or 'R' "[LR]"
  • followed by zero or more arbitrary characters ".*"

Upvotes: 4

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

regexp_replace(string, '^.*?-(\d+)[LR].*$', '\1')

fiddle

Upvotes: 4

Related Questions