Oracle Substring after specific character

Question

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?

Egor Skriptunoff · Accepted Answer · 2013-04-09 09:02:27Z

4

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

fiddle

answered Apr 9, 2013 at 9:02

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jochen Birkle Over a year ago

That did the trick, thank you! Very confusing syntax when one is not that deep into that... at least when creating a statement. Understanding is ok with the doku beside me =)

Frank Schmitt · Accepted Answer · 2013-04-09 10:11:51Z

4

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 ".*"

answered Apr 9, 2013 at 10:11

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Collectives™ on Stack Overflow

Oracle Substring after specific character

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related