在 PostgreSQL 中搜索子字符串

我有一个 POstgreSQL 8.4。 我有一个表，我想使用子查询返回的子字符串（字符变化数据类型）在该表的一行（字符变化数据类型）中查找字符串：

SELECT  uchastki.kadnum
FROM  uchastki
WHERE kadnum LIKE (
    SELECT str
    FROM test
    WHERE str IS NOT NULL)

但出现错误

ERROR:  more than one row returned by a subquery used as an expression

现场test.str我有像这样的字符串66:07:21 01 001 in uchastki.kadnum 66:07:21 01 001:27.

如何使用子查询的结果查找子字符串？

UPDATE

表测试：

CREATE TABLE test
(
    id serial NOT NULL,
    str character varying(255)
)
WITH (
    OIDS=FALSE
);
ALTER TABLE test OWNER TO postgres;

表乌恰斯特基：

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;

Use like any :

SELECT  uchastki.kadnum
FROM  uchastki
WHERE kadnum LIKE  ANY(
   SELECT str
   FROM test
WHERE str IS NOT NULL)

也许：

SELECT  uchastki.kadnum
FROM  uchastki
WHERE kadnum LIKE  ANY(
   SELECT '%' || str || '%'
   FROM test
WHERE str IS NOT NULL)

这是一个很好的功能，您可以使用不同的运算符，例如= any (select ... ), or <> all (select...).