CODEWITHSUNDEEP
sqlregexoracle-databaseoracle11gregex-lookarounds
Kenny
Kenny

Reputation: 1982

Look ahead in regular expression

Is there something such as look-ahead, look-behind in Oracle's regular expression such as ?<= in some languages ?

For example we want to look for "ef" only if before it is "ab" and not "cd".

In REGEXP_REPLACE we can do this by refering to the nth group but how about REGEXP_INSTR and REGEXP_SUBSTR ?

Example: look for position of ef if it is a abef and not when it is a cdef. I know for this example, we can look for abef and offset by 2, but this is a simple case. Some times it is not possible to offset. I don't have a good scenario at hand but let's say : Look for ef on ly if before it is 2 to 5 times of the following characters a or b or 2 ([ab2]){2,5}

Upvotes: 0

Views: 973

Answers (1)

MT0
MT0

Reputation: 167972

look for "ef" only if before it is "ab" and not "cd"

If before it is ab then before it cannot be cd so you can just do:

REGEXP_REPLACE( text, '(ab)ef', '\1XX' )

or

REGEXP_SUBSTR( text, 'ab(ef)', 1, 1, NULL, 1 )

or

REGEXP_INSTR( text, 'abef', 1, 1, 1, NULL ) - LENGTH( 'ef' )

Look for ef on ly if before it is 2 to 5 times of the following characters a or b or 2 ([ab2]){2,5}

REGEXP_REPLACE( ly, '([ab2]{2,5})ef', '\1XX' )

or

REGEXP_SUBSTR( ly, '[ab2]{2,5}(ef)', 1, 1, NULL, 1 )

or

REGEXP_INSTR( ly, '[ab2]{2,5}ef', 1, 1, 1, NULL ) - LENGTH( 'ef' )

looking for [efg]{4,6} only if before it is [ab2]{2,5}

REGEXP_REPLACE( ly, '([ab2]{2,5})[efg]{4,6}', '\1XX' )

or

REGEXP_SUBSTR( ly, '[ab2]{2,5}([efg]{4,6})', 1, 1, NULL, 1 )

or

REGEXP_INSTR( ly, '[efg]{4,6}', REGEXP_INSTR( ly, '[ab2]{2,5}[efg]{4,6}' ) )

Upvotes: 3

Related Questions