Search code examples
sqloracle11gxregexp

How do I select everything after a certain position using REGEXP in Oracle

I am using Oracle 11G and I have the following string: I - Am in- Need Help- Please and want to parse the string using the - character then select everything after the second object. I have been playing around with REGEXP_REPLACE and SUBSTR and I can select the second object but can't seem to select everything after the second object. I know I need to add a * somewhere I think but I can't seem to get the syntax correct. Any help would be greatly appreciated.

SELECT REGEXP_SUBSTR ('I - Am in- Need of Help- Please', '[^-]+' , 1, 2) 
  FROM DUAL;

The above query returns the second object 'Am in' but I want this returned 'Need of Help- Please'. It is also important to note that I don't want the - character directly following the second object in the result either but I want to start with the third object.

Solution

  • Since [^-] will only match non-hyphen characters, your match group will start after the first hyphen and then stop before the next one. To capture everything following the first hyphen, try:

     SELECT REGEXP_REPLACE(REGEXP_SUBSTR ('I - Am in- Need of Help- Please', '-.+' , 1), '-', '', 1, 1) FROM DUAL;