如何通过ANY数组运算符使用正则表达式 [英] How to use regular expression with ANY array operator

问题描述

我有一列包含作者数组。如何使用〜* 运算符检查其值是否与给定的正则表达式匹配？

I have a column containing an array of authors. How can I use the ~* operator to check if any of its values match a given regular expression?

〜* 运算符在左边检查字符串，在右边匹配正则表达式。文档说 ANY 运算符必须在右边，所以很明显

The ~* operator takes the string to check on the left and the regular expression to match on the right. The documentation says the ANY operator has to be on the right side so, obviously

SELECT '^p' ~* ANY(authors) FROM book;

不起作用，因为PostgreSQL尝试匹配字符串 ^ p 反对数组中包含的表达式。

does not work as PostgreSQL tries to match the string ^p against expressions contained in the array.

有什么想法吗？

推荐答案

第一个显而易见的想法是使用带有转换参数的正则表达式匹配运算符：

The first obvious idea is to use your own regexp-matching operator with commuted arguments:

create function commuted_regexp_match(text,text) returns bool as
'select $2 ~* $1;'
language sql;

create operator ~!@# (
 procedure=commuted_regexp_match(text,text),
 leftarg=text, rightarg=text
);

然后，您可以这样使用它：

Then you may use it like this:

SELECT '^p' ~!@# ANY(authors) FROM book;

---

另一种不同的看待方法数组并在SQL中公式化为ANY构造：

---

Another different way of looking at it to unnest the array and formulate in SQL the equivalent of the ANY construct:

select bool_or(r) from 
  (select author ~* '^j' as r
    from (select unnest(authors) as author from book) s1 
  ) s2;

这篇关于如何通过ANY数组运算符使用正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！