postgresql 如果字符串包含列值,则选择

5t7ly7z5  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(3)|浏览(150)
Manufacturer
==========================
id            name      
--------------------------
1             Company Inc.
2             Google Test.
3             3M (UNITY) USA. INC.
4             CE EE

字符串
例如,我有一个字符串 'Google Test。1257 SCS RANDOM 31233DD',我想找到表manufacturer中的所有行,其中ht name是给定字符串的一部分:

SELECT * FROM manufacturer
WHERE 'Google Test. 1257 SCS RANDOM 31233DD' ILIKE '%' || name || '%'


正确返回:

id            name      
--------------------------
2             Google Test.


但当我这样做:

SELECT * FROM manufacturer
WHERE '3dad QTICE EEN ' ILIKE  '%' || name || '%'


它返回:

id            name      
--------------------------
4             CE EE


我不想要这样的部分匹配。name在单词中间不应匹配。我试了substring()

SELECT * from manufacturer
WHERE  SUBSTRING('Google Test. 1257 SCS RANDOM 31233DD' from name) != '';


但我得到:

ERROR: invalid regular expression: quantifier operand invalid


不幸的是,我没有确切的规格去关闭,因为我查询这个关闭外部数据库。但从我所看到的,列是varchar(256)。所有值都是大写的,并使用普通空格。所有字符都以字符或数字开头,以数字、char或特殊字符结尾。例如:“克利夫兰钻(绿色)”。值中包含特殊字符,例如,.()&/
我并不是真的在寻找效率,只要它不需要超过50毫秒做一个查询。
截至目前,大约有10000+条目,但它可能会随着时间的推移而增长。

deikduxw

deikduxw1#

使用LIKE的一种方法是在开头和结尾添加空格:

SELECT *
FROM db
WHERE ' ' || '3dad QTICE EEN ' || ' ' ILIKE  '% ' || manufacturer || ' %'

字符串
如果您需要更复杂的匹配,那么您可能需要使用带单词边界的正则表达式。

zpjtge22

zpjtge222#

要解决这个问题,你真的需要使用正则表达式,因为在字符串的任何一侧添加空格都不会在行的开头或结尾处匹配。通过使用正则表达式,我们也可以检查这种情况。举例来说:

SELECT *
FROM manufacturer
WHERE 'Google Test. 1257 36700 SCS RANDOM WORD 31233DD' ~ ('(^| )' || name || '( |$)');

字符串
输出量:

id  name
2   Google Test.


查询:

SELECT *
FROM manufacturer
WHERE '3dad QTICE EEN ' ~ ('(^| )' || name || '( |$)');


输出量:

There are no results to be displayed.


查询:

SELECT *
FROM manufacturer
WHERE 'CE EE ' ~ ('(^| )' || name || '( |$)');


输出量:

id  name
4   CE EE


Demo on dbfiddle

更新

由于表中的name值可能包含在正则表达式中具有特殊含义的字符,因此在将名称包含到正则表达式中之前需要对它们进行转义。你可以用REGEXP_REPLACE来实现。

REGEXP_REPLACE(name, '([\\.+*?[^\]$(){}=!<>|:\-#])', '\\\1', 'g')


所以你的查询应该是:

SELECT *
FROM manufacturer
WHERE 'Google Test. 1257 36700 SCS RANDOM WORD 31233DD' ~ ('(^| )' || REGEXP_REPLACE(name, '([\\.+*?[^\]$(){}=!<>|:\-#])', '\\\1', 'g') || '( |$)');


Updated demo

uqxowvwt

uqxowvwt3#

所有值都以字符或数字开头,以数字、char或特殊字符结尾。...值中有特殊字符,例如,.()&/
我建议使用正则表达式匹配操作符**~。在name中仔细定义边界转义特殊字符**:
创建 * 一次 *:

CREATE OR REPLACE FUNCTION f_regexp_escape(text)
  RETURNS text AS
$func$
SELECT regexp_replace($1, '([!$()*+.:<=>?[\\\]^{|}-])', '\\\1', 'g')
$func$  LANGUAGE sql IMMUTABLE;

字符串
然后:

SELECT * FROM manufacturer
WHERE  '3dad QTICE EEN ' ~ ('\m' || f_regexp_escape(name) || '( |$)')


怎么会?为什么?
\m..单词的开头。工作,自:* 值以字符或数字开头 *
( |$)..空格或字符串的结尾。我们需要它,因为价值观:* 以数字、字符或特殊字符结尾 *
manufacturer.name的内容是 * 模式 * 的核心。您需要其所有字符的字面意义,因此通过适当的转义来去除任何特殊意义。对于LIKE(少数特殊字符)和正则表达式匹配运算符~(更多特殊字符)都是如此。经常被忽视,而且是一个陷阱。这让你(和边界的棘手定义)。读这个!

  • 正则表达式或LIKE模式的转义函数

然后使用函数f_regexp_escape(),如图所示。一个name像:

3M (UNITY) USA. INC.


变成:

3M \(UNITY\) USA\. INC\.


将容易转义的模式存储在表manufacturer中可能比较方便,可以作为附加列。也许加上这样的填充:

\m3M \(UNITY\) USA\. INC\.( |$)


或者像演示的那样动态生成模式。
这样name可以是一个单词或一个完整的短语,并以任何字符结束。但是开始和结束永远不会在另一边的一个“单词”的中间匹配。
Postgres中还有其他模式匹配工具:

如果您的表很大,请考虑 * full text search * 基础架构,该架构具有优化的索引和 * 短语搜索 * 功能:

相关问题