Oracle SQL : Matching Prefix and Suffix string but only accepting whats in between

regex replace

A while ago I wrote an article about regex lookahead and lookbehind here. I needed the same thing now in SQL, specifically Oracle flavor. Turns out that Oracle supports the POSIX BRE syntax and POSIX ERE syntax plus PERL influenced extensions, but no look ahead look behind equivalent regex syntax.

So the problem we have to solve is the same. I have a String that is ";" seperated key value pairs that might look like below:

Regex Lookahead and Lookbehind : Match Prefix and Suffix string but only accepting anything in between

Reg ex, lookahead, lookbehind

So there is a little known construct in regex of lookahead and lookbehind, that java regex also supports atleast upwards from java 5. This is exactly what you need if the desired match lives between a known prefix and suffix String. Or depending on if you have a prefix you can use the lookbehind construct and if you have a suffix only, you can use the lookahead construct.

So the lookbehind is represented by (?<=X) and lookahead is represented by (?=X).

Subscribe to RSS - regex