2

I have the following string 011/2020-PL00-70-31 (it could slightly different for example 011/2020-PL00-70-3 or 011/2020-PL00-70-310). I need to extract from the string all string before last -. As a result of REGEXP_SUBSTR of 011/2020-PL00-70-310 I need to get 011/2020-PL00-70 only, i.e. without last 4 symbols (but in some case it could be without 2 or 3 symbols).

I am new to regular expression in PL SQL, so sorry for question if it is so easy.

Thanks a lot.

Peter B
  • 22,460
  • 5
  • 32
  • 69
Davit
  • 97
  • 7
  • You might try `REGEXP_REPLACE(whatever, '(-[^-]+)$', '')`. – Aconcagua Dec 16 '21 at 15:58
  • The expression `REGEXP_REPLACE(whatever, '(-[^-]+)$')` finds `-31`, but not remove. I cannot apply `replace` because there could be another matching of `-31` in the string. – Davit Dec 16 '21 at 16:15
  • It should find a minus followed by at least one non-minus – but *only* at the *end* of the string (`$`!), so any intermediate occurrence should remain untouched. – Aconcagua Dec 16 '21 at 16:22
  • @Aconcagua, Please post your comment as an answer. This is the correct solution. – Ankit Bajpai Dec 27 '21 at 15:35
  • @AnkitBajpai Added – didn't post before because I hadn't tested... – Aconcagua Jan 01 '22 at 09:37

3 Answers3

0

In this particular case I think you're overthinking your solution using regex. Your hyphen is always the third hyphen so you can just use INSTR to find the third occurrence. See this query here:

SELECT SUBSTR(sample, 1, INSTR(sample, '-', 1, 3)-1) AS match
FROM sample_table;

I have linked my results with a SQLFiddle - http://sqlfiddle.com/#!4/c30207/7/0

DonkeyKongII
  • 431
  • 2
  • 8
0

You can use a regex capture group () to get only the part you need from a pattern.

SELECT REGEXP_SUBSTR(sample, '^(.*)-\d+$',1,1,'',1) AS sample2
FROM sample_table
SAMPLE2
011/2020-PL00-70
011/2020-PL00-70
011/202-PL00-70

Demo on db<>fiddle here

Test of the regex pattern here

LukStorms
  • 28,916
  • 5
  • 31
  • 45
0

You might just replace the trailing digits away:

REGEXP_REPLACE(whatever, '(-[^-]+)$', '')

The regex catches a minus sign and any subsequent non-minus characters ([^-]+; alternatively you can match against digits only: \d+) – if they are located at the end of the string ($), so intermediate digits are protected.

Aconcagua
  • 24,880
  • 4
  • 34
  • 59