CODEWITHSUNDEEP
sqlregexoracle-database
Roberto
Roberto

Reputation: 103

Oracle regex for all characters before a specific string

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.

Upvotes: 0

Views: 2709

Answers (1)

Jan
Jan

Reputation: 43169

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.

Upvotes: 2

Related Questions