How to extract group from regular expression in Oracle?

by

The third parameter of the REGEXP_SUBSTR function indicates the position in the target string (de_desc in your example) where you want to start searching. Assuming a match is found in the given portion of the string, it doesn’t affect what is returned.

In Oracle 11g, there is a sixth parameter to the function, that I think is what you are trying to use, which indicates the capture group that you want returned. An example of proper use would be:

SELECT regexp_substr('abc[def]ghi', '\[(.+)\]', 1,1,NULL,1) from dual;

Where the last parameter 1 indicate the number of the capture group you want returned. Here is a link to the documentation that describes the parameter.

10g does not appear to have this option, but in your case you can achieve the same result with:

select substr( match, 2, length(match)-2 ) from (
SELECT regexp_substr('abc[def]ghi', '\[(.+)\]') match FROM dual
);

since you know that a match will have exactly one excess character at the beginning and end. (Alternatively, you could use RTRIM and LTRIM to remove brackets from both ends of the result.)

More Related Contents:

  1. The most efficient way to UPPER CASE two columns in a single query [closed]
  2. Current date in Oracle Db
  3. Merging two tables in SQL [closed]
  4. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  5. Inner join vs Where
  6. Case insensitive searching in Oracle
  7. Get value based on max of a different column grouped by another column [duplicate]
  8. How to find the employee with the second highest salary?
  9. Oracle: What does `(+)` do in a WHERE clause?
  10. Concatenate results from a SQL query in Oracle
  11. Generate a range of dates using SQL
  12. SQL recursive query on self referencing table (Oracle)
  13. PLS-00428: an INTO clause is expected in this SELECT statement
  14. Why use a JOIN clause versus a WHERE condition?
  15. format interval with to_char
  16. How to kill a running SELECT statement
  17. Equivalent function for DATEADD() in Oracle
  18. Anonymous TABLE or VARRAY type in Oracle
  19. How do I convert a “legacy” left outer join statement in Oracle?
  20. Oracle “Partition By” Keyword
  21. How to Alter Constraint
  22. Group by with CLOB in select-statement
  23. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  24. Using LIKE in an Oracle IN clause
  25. How to handle optional parameters in SQL query?
  26. How to check if a column exists before adding it to an existing table in PL/SQL?
  27. How can I solve ORA-00911: invalid character error?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment