How do I compare multiple fields to multiple substrings?

user_3357935 0
Viewed 39

I'm working on a Presto query that checks multiple fields against multiple substrings to see if at least one field contains any of the given substrings. For example, let's say I want to check if either column1, column2, or column3 contain test, inactive, or deprecated.

I could write multiple LIKE comparisons for each field and substring, but it seems a bit repetitive.

-- Functional, but cumbersome
SELECT *
FROM table
WHERE
    column1 LIKE '%test%' OR column1 LIKE '%inactive%' OR column1 LIKE '%deprecated%'
    OR column2 LIKE '%test%' OR column2 LIKE '%inactive%' OR column2 LIKE '%deprecated%'
    OR column3 LIKE '%test%' OR column3 LIKE '%inactive%' OR column3 LIKE '%deprecated%'

I can simplify it a bit with regexp_like() but it's still a bit repetitive.

-- Functional, less cumbersome
SELECT *
FROM table
WHERE
    REGEXP_LIKE(column1, 'test|inactive|deprecated')
    OR REGEXP_LIKE(column2, 'test|inactive|deprecated')
    OR REGEXP_LIKE(column3, 'test|inactive|deprecated')

Ideally I'd like to have a single comparison that covers each field and substring.

-- Non functional pseudocode
SELECT *
FROM table
WHERE (column1, column2, column3) LIKE ('%test%', '%inactive%', '%deprecated%')

Is there a simple way to compare multiple fields to multiple substrings?

presto sql-like sql
1 Answers

You could search on a concatenation of the three columns.

SELECT *
FROM table
WHERE
    REGEXP_LIKE(column1+' ' + column2+' ' +column3, 'test|inactive|deprecated')

Also you could put the words your matching against as rows in a new MatchWord table, then be able to add/remove words without changing your query.

SELECT 
* 
FROM 
 Data d 
WHERE 
EXISTS(
    SELECT 
        * 
    FROM MatchWord w 
    WHERE 
        d.column1+' ' +d.column2+' ' +d.column3 LIKE '%' + w.word + '%'
)
user_70143750
Related