Oracle regex for all characters before a specific string

Question

I'm working on a substring regular expression and I need to get all characters from a string like this:

FIRSTCOLUMN, SECONDCOLUMN, THIRDCOLUMN

So far I've got:

SELECT 
    REGEXP_SUBSTR('FIRSTCOLUMN, SECONDCOLUMN, THIRDCOLUMN', '[^SECONDCOLUMN]+') AS SUBSTRING 
FROM DUAL;

This returns just "FIR" but I need the result to be "FIRSTCOLUMN, ".

Another example to understand better the question is: when I have '[^THIRDCOLUMN]+' pattern instead of '[^SECONDCOLUMN]+' as the first example, I want the result to be "FIRSTCOLUMN, SECONDCOLUMN, ".

I know it is possible but I'm not good enough on regular expressions.

Jan · Accepted Answer · 2017-04-05 09:38:23Z

2

You are getting basic regular expressions concepts wrong. The [^...] negates all characters inside the class, so it looks for characters not S, not E, not C, etc which correctly stops at FIRST (see the S).
What you possibly want is a lazy quantifier:

^.*?SECONDCOLUMN

or

^.*?THIRDCOLUMN

respectively.

answered Apr 5, 2017 at 9:38

Jan

43.3k11 gold badges57 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

collapsar Over a year ago

Technically, you need some syntactic sugar to exclude the delimiting string from being part of the result: either wrap it with a replace(..., 'secondcolumn') or use select regexp_substr ( 'firstcolumn, secondcolumn, thirdcolumn', '^(.*?)secondcolumn', 1, 1, null, 1 ) from dual; The additional arguments denote match offset, occurrence, match parameters, capture group to be returned (oracle 12c only). Consult oracle docs for detailed info. If any term is double dutch to you, consider to take a tour to a site explaining regexen (eg. regular-expressions.info).

Roberto Over a year ago

@Jan Thanks for the suggestion. But I wanted to exclude the SECONDCOLUMN string of the first example. So, using your suggestion, my code became: SELECT REGEXP_SUBSTR('FIRSTCOLUMN, SECONDCOLUMN, THIRDCOLUMN', '^.*?SECONDCOLUMN') AS SUBSTRING FROM DUAL; But my result is: FIRSTCOLUMN, SECONDCOLUMN In my case I wanted the result to be: FIRSTCOLUMN, ** That's why I put the '[^...]', to exculde "SECONDCOLUMN" from "FIRSTCOLUMN, **SECONDCOLUMN" . Thank you in advice.

Collectives™ on Stack Overflow

Oracle regex for all characters before a specific string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related