在 PostgreSQL 中搜索子串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12952581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Searching substring in PostgreSQL
提问by Kliver Max
I have a POstgreSQL 8.4. I have a table and i want to find a string in one row (character varying datatype) of this table using substring (character varying datatype) returned by subquery:
我有一个 POstgreSQL 8.4。我有一个表，我想使用子查询返回的子字符串（字符变化数据类型）在该表的一行（字符变化数据类型）中找到一个字符串：
SELECT uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE (
SELECT str
FROM test
WHERE str IS NOT NULL)
But get a error
但得到一个错误
ERROR: more than one row returned by a subquery used as an expression
In field test.stri have strings like 66:07:21 01 001in uchastki.kadnum66:07:21 01 001:27.
在字段中， test.str我有类似66:07:21 01 001in 的字符串uchastki.kadnum66:07:21 01 001:27。
How to find substring using results of subquery?
如何使用子查询的结果查找子字符串？
UPDATE
更新
Table test:
表测试：
CREATE TABLE test
(
id serial NOT NULL,
str character varying(255)
)
WITH (
OIDS=FALSE
);
ALTER TABLE test OWNER TO postgres;
Table uchastki:
表 uchastki：
CREATE TABLE uchastki
(
fid serial NOT NULL,
the_geom geometry,
id_uch integer,
num_opora character varying,
kod_lep integer,
kadnum character varying,
sq real,
kod_type_opora character varying,
num_f11s integer,
num_opisanie character varying,
CONSTRAINT uchastki_pkey PRIMARY KEY (fid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2)
)
WITH (
OIDS=FALSE
);
ALTER TABLE uchastki OWNER TO postgres;
回答by maniek
Use like any:
使用like any：
SELECT uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE ANY(
SELECT str
FROM test
WHERE str IS NOT NULL)
Or perhaps:
也许：
SELECT uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE ANY(
SELECT '%' || str || '%'
FROM test
WHERE str IS NOT NULL)
this is a nice feature, You can use different operators, for example = any (select ... ), or <> all (select...).
这是一个不错的功能，您可以使用不同的运算符，例如= any (select ... ), 或<> all (select...)。
回答by Craig Ringer
I'm going to take a wild stab in the dark and assume you mean that you want to match a string Safrom table Aagainst one or more other strings S1 .. Snfrom table Bto find out if any of the other strings in S1 .. Snis a substring of Sa.
我将在黑暗中进行大胆的尝试，并假设您的意思是要将Satable中的字符串与 tableA中的一个或多个其他字符串进行匹配S1 .. Sn，B以找出其中的任何其他字符串S1 .. Sn是否是Sa.
A simple example to show what I mean (hint, hint):
一个简单的例子来说明我的意思（提示，提示）：
Given:
鉴于：
CREATE TABLE tableA (string_a text);
INSERT INTO tableA(string_a) VALUES
('the manual is great'), ('Chicken chicken chicken'), ('bork');
CREATE TABLE tableB(candidate_str text);
INSERT INTO tableB(candidate_str) VALUES
('man'),('great'),('chicken');
I want the result set:
我想要结果集：
the manual is great
chicken chicken chicken
because the manual is greathas manand greatin it; and because chicken chicken chickenhas chickenin it. There is no need to show the substring(s) that matched. borkdoesn't match any substring so it is not found.
因为the manual is great有man和great在里面；因为里面chicken chicken chicken有chicken。无需显示匹配的子字符串。bork不匹配任何子字符串，因此未找到。
Here's a SQLFiddlewith the sample data.
这是带有示例数据的SQLFiddle。
If so, shamelessly stealing @maniek's excellent suggestion, you would use:
如果是这样，无耻地窃取@maniek 的绝妙建议，您将使用：
SELECT string_a
FROM tableA
WHERE string_a LIKE ANY (SELECT '%'||candidate_str||'%' FROM tableB);
(Vote for @maniek please, I'm just illustrating how to clearly explain - I hope - what you want to achieve, sample data, etc).
（请为@maniek 投票，我只是在说明如何清楚地解释 - 我希望 - 您想要实现的目标，示例数据等）。
回答by Craig Ringer
(Note: This answer was written before further discussion clarified the poster's actual intentions)
（注：此答案是在进一步讨论澄清发布者的实际意图之前写的）
It would appear highly likely that there is more than one strin testwhere str IS NOT NULL. That's why more than one row is returned by the subquery used as an expression, and, thus, why the statement fails.
这样看来很可能有不止一个str的test地方str IS NOT NULL。这就是用作表达式的子查询返回多于一行的原因，也是该语句失败的原因。
Run the subquery stand-alone to see what it returns and you'll see. Perhaps you intended it to be a correlated subquerybut forgot the outer column-reference? Or perhaps there's a column also called strin the outer table and you meant to write:
单独运行子查询以查看它返回的内容，您将看到。也许您打算将它作为相关子查询，但忘记了外部列引用？或者也许str在外部表中也有一个列，你打算写：
SELECT uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE (
SELECT test.str
FROM test
WHERE uchastki.str IS NOT NULL)
?
?
(Hint: Consistently using table aliases on column references helps to avoid name-clash confusion).
（提示：在列引用上始终使用表别名有助于避免名称冲突混淆）。
