0

So I have a string like abcdefg#3h#ijkl12mka#123456#L#5

I am trying to get only the string 123456 out of that string. Note: there are string of different length, so I cant use something like substr(21,length(string)). I do not think i can use something like substr(-11,-1). Does anyone have any advice on how to approach this?

  • take a look at regular expressions. – OldProgrammer Jun 27 '19 at 13:41
  • 2
    You'll need to explain the logic behind choosing "123456". – mustaccio Jun 27 '19 at 13:48
  • If it's the nth string you're after, use [this](https://stackoverflow.com/a/31464699/7998591) solution – Kaushik Nayak Jun 27 '19 at 13:52
  • that 123456 is the string I need returned. there are other cases where it might be abcdefg#3123h#i4kl12#mka#123456#L#5 - just different lengths but i need that 123456 which is always that length towards the end. –  Jun 27 '19 at 14:40
  • 2
    "towards the end" is too generic. You need to be able to describe how to find the string you need so it will work every time. "The 3rd pound sign-delimited field from the end" or "the only field containg > 1 digit no matter where it appears on in the string" or "the last all-digit field that is exactly 6 digits long". What should be returned if the string is not found? NULL? The entire string? – Gary_W Jun 27 '19 at 15:18
  • The 2nd # is the start position and the 3rd # is the end position. Inside should be only numeric characters. If there is not any should return null. –  Jun 27 '19 at 15:22

2 Answers2

0

Just take a look here regex.

With this you can define your string which is always there. In your case for example you want to have the string which is surrounded by # and contains only numbers...

  • The way to use this in an oracle database is explained here.

  • A site where you can test your regex is here.

  • As well as an already answered question here.

0

Try this:

select REGEXP_SUBSTR('abcdefg#3h#ijkl12mka#123456#L#5','[^#]+',1,4) from dual;

Demo

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31