Search code examples
sqloracle-databaseregexp-substr

How to apply `REGEXP_SUBSTR` to extract specific substring from string?

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.

Solution

  • 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