sqlregexlistprestoregexp-like

How to check if all items in a group are contained in a string for many items? - RegEx

I'm planning on embedding RegEx in my SQL query so can't really use a loop for this.

Essentially, I'm trying to check a series of groups to see if the name of my column contains all the attributes of any of those individual groups (i.e. groups must be checked against independently and not altogether).

For example,

group1 = l, w, q
group2 = o, l, d

If the column name contains all the items in group1 (e.g. low_quality) should return true. Similarly, cold would also return true since it matches against every item in group2.

How would I go about validating whether or not my column name contains every item in any group? (items can be >1 character and I have around 40 groups to test against).

Could something along the following lines be modified?

SELECT column_name,
       (CASE WHEN REGEXP_LIKE(column_name, ((?=l)(?=w)(?=q))|((?=o)(?=l)(?=d))) THEN true ELSE NULL
        END) AS flag
FROM information_schema.columns;

I'm just not sure how to check against multiple groups independently.

Solution

  • You can use

    REGEXP_LIKE(column_name, '^(?:(?=.*l)(?=.*w)(?=.*q)|(?=.*o)(?=.*l)(?=.*d))')

    Details: