CODEWITHSUNDEEP
postgresql
Kliver Max
Kliver Max

Reputation: 5299

Searching substring in PostgreSQL

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:

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.str i have strings like 66:07:21 01 001 in uchastki.kadnum 66: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:

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;

Upvotes: 1

Views: 14311

Answers (3)

maniek
maniek

Reputation: 7307

Use 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...).

Upvotes: 13

Craig Ringer
Craig Ringer

Reputation: 324285

I'm going to take a wild stab in the dark and assume you mean that you want to match a string Sa from table A against one or more other strings S1 .. Sn from table B to find out if any of the other strings in S1 .. Sn is a substring of 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 great has man and great in it; and because chicken chicken chicken has chicken in it. There is no need to show the substring(s) that matched. bork doesn't match any substring so it is not found.

Here's a SQLFiddle with the sample data.

If so, shamelessly stealing @maniek's excellent suggestion, you would use:

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).

Upvotes: 3

Craig Ringer
Craig Ringer

Reputation: 324285

(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 str in test where 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.

Run the subquery stand-alone to see what it returns and you'll see. Perhaps you intended it to be a correlated subquery but forgot the outer column-reference? Or perhaps there's a column also called str in the outer table and you meant to write:

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).

Upvotes: 1

Related Questions