Regex into Oracle expression - (\b|\d)(?i)INT(\b|\d)

user_542103 0
Viewed 73

I have a regex expression as such:

(\b|\d)(?i)INT(\b|\d)

It works as I'd like on https://regex101.com/

But when trying to make it work with Oracle's Regex... I started with this:

SELECT REGEXP_INSTR('INT', '(\b|\d)(?i)INT(\b|\d)') AS FOUND FROM DUAL WHERE 1=1 ;

and various variations, but not having success, was hoping to get some help

Note the first parameter 'INT' would of course be a field in a table that I wanted to search in, etc

oracle regex
1 Answers

If you plan to just check for a match, you can "decompose" \b into (^|\W|\d) / ($|\W|\d) and use:

SELECT REGEXP_INSTR('INT', '(^|\W|\d)INT($|\W|\d)', 1, 1, 0, 'i')

Here, (^|\W|\d)INT($|\W|\d) matches

  • (^|\W|\d) - start of string, or a non-word char, or a digit
  • INT - an INT string
  • ($|\W|\d) - end of string, or a non-word char, or a digit.

The sixth argument to REGEXP_INSTR is the match parameter argument, 'i' means case insensitive. So, this function will return the position of the first character of the first occurrence (see the fourth occurrence argument) of the match (see the fifth return_option argument), that is searched from the first char in the string (see the third position argument)

To return the exact position of INT using this single regex approach is not possible, since \W will consume a non-word char at the start.

user_38329700
Related