Search code examples
regexoracle-databaseregex-lookaroundsregexp-like

oracle regexp_like: using Perl regexp for AND operator equivalent

Is there an equivalent of the AND operator when writing a regular expression within Oracle REGEX_LIKE? I’ve been trying to select all cases where all three words must be contained within a string. In the example below, I want to match all instances where all three words, small & leather & goods must be included in the string. I’ve tried many online regex testers, and the regex syntax does precisely what I need it to do, but when I try and use the syntax within a REGEXP_LIKE expression, I get back zero matches 

regexp_like(GLOBAL_CATEGORY,'(?=.*small)(?=.*leather)(?=.*goods)^.*$','i')

regex tester example that accomplishes what I'm looking for: regexr.com/4vc1s

Men/Outerwear/Leather/Sale **(NO MATCH)**
Men > Accessories > Bags & Leather Goods > Small Leather Goods **(YES MATCH)**
Men > Accessories > Bags & Leather Goods > Bags **(NO MATCH)**
Men > Accessories > Bags & Leather Goods > Small Leather Goods **(YES MATCH)**
Men/Outerwear/Leather/Sale **(NO MATCH)**
Men/Small_Leather_Goods/Sale **(YES MATCH)**
Men/Outerwear/Leather **(NO MATCH)**
Men/Small_Leather_Goods **(YES MATCH)**
men>accessories>small>leather>goods **(YES MATCH)**
Solution

  • Assuming you want the words small, leather and goods in that order, please try :

    regexp_like(GLOBAL_CATEGORY,'*small.*.leather.*goods*', 'i')

    . matches any character
    * matches zero or more occurrences of the preceding subexpression
    i is a match pattern which specifies case-insensitive matching

    Demo here