如何将多个字段与多个子字符串进行比较？

发布于 2025-02-03 18:58:09 字数 1107 浏览 3 评论 0原文

我正在研究一个Presto查询，该查询检查多个子字符串，以查看至少一个字段是否包含任何给定的子字符串。例如，假设我想检查column1，column2或column3包含 test ，>不活动，或弃用。

我可以为每个字段和子字符串编写多个喜欢比较，但似乎有些重复。

-- 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%'

我可以使用Regexp_like（）进行一些简化的内容，但它仍然有些重复。

-- 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')

理想情况下，我想进行一次涵盖每个字段和子字符串的比较。

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

是否有一种简单的方法将多个字段与多个基因进行比较？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情未る 2025-02-10 18:58:10

您可以搜索三列的串联。

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

另外，您可以将匹配的单词与新的MatchWord表中的行相吻合，然后能够添加/删除单词而无需更改查询。

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

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 + '%'
)

回复收藏 0 原文

~没有更多了~