主页 / dba / 问题 / 185454
Accepted
Roots
Asked: 2017-09-09 11:13:05 +0800 CST

SQL 正则表达式 PostgreSQL

  • 772

让我们考虑这样一个表：

id |            name
 1 | This is a test sentence
 2 | is a sentence TeST
 3 | This a IS test sentence
 4 | iS a tEst sentence tHis
 5 | This a test sentence is

我怎样才能得到所有的行，搜索“是一个句子测试”？

我在用：

SELECT *
FROM table
WHERE name ILIKE '%is%' AND name ILIKE '%a%' AND name ILIKE '%sentence%' AND name ILIKE '%test%'

但这似乎不是最好的方法。

postgresql string

2 个回答

  1. 你有LIKE和ILIKE和。这涵盖了 SQL-LIKE 条件语句和更强大、更强大的类似正则表达式的功能。~~*

    SELECT
 s,
 s LIKE '%is a sentence test%' as "like",
 s ILIKE '%is a sentence test%' AS "ilike",
 s ~ '.*is a sentence test.*' AS "~",
 s ~* '.*is a sentence test.*' AS "~*"
FROM ( VALUES
 ( 'This is a test sentence' ),
 ( 'is a sentence TeST' ),
 ( 'This a IS test sentence' ),
 ( 'iS a tEst sentence tHis' ),
 ( 'This a test sentence is' )
) AS t(s);

            s            | like | ilike | ~ | ~* 
-------------------------+------+-------+---+----
 This is a test sentence | f    | f     | f | f
 is a sentence TeST      | f    | t     | f | t
 This a IS test sentence | f    | f     | f | f
 iS a tEst sentence tHis | f    | f     | f | f
 This a test sentence is | f    | f     | f | f
(5 rows)

    您没有使用任何高级正则表达式功能，所以我会选择LIKE, 或ILIKE。

    • 1
  2. Best Answer

    首先，您使用 split_to_array 函数将字符串放入数组 ( https://www.postgresql.org/docs/9.6/static/functions-string.html )，然后您可以比较它们。Postgres 有一个很酷的函数来检查一个数组是否包含另一个数组。有关 Postgres 数组的更多信息，请访问：https ://www.postgresql.org/docs/9.5/static/functions-array.html

    表格设置：

    CREATE TABLE stringchecks
(
    id serial,
    name varchar(100)
);

INSERT INTO stringchecks (name) VALUES ('This is a test sentence');
INSERT INTO stringchecks (name) VALUES  ('is a sentence TeST');
INSERT INTO stringchecks (name) VALUES ('This a IS test sentence');
INSERT INTO stringchecks (name) VALUES ('iS a tEst sentence tHis');
INSERT INTO stringchecks (name) VALUES ('This a test sentence is');

    检查匹配你有两个选择。您可以使用 regexp_split_to_array：

    SELECT
    id,
    name,
    regexp_split_to_array(lower(name), E'\\s+') @> 
       regexp_split_to_array(lower('is a sentence test'), E'\\s+') 
FROM stringchecks;

    或者您可以使用 string_to_array：

    SELECT
    id,
    name,
    string_to_array(lower(name), ' ') @> 
       string_to_array(lower('is a sentence test'), ' ')
FROM stringchecks;

    在这种情况下，因为它是一个简单的定界符，所以使用 string_to_array 会更快。http://www.postgresonline.com/journal/archives/370-regexp_split_to_table-and-string_to_array-unnest-performance.html

    • 1

相关问题