Searching substring in PostgreSQL

Question

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;

Recognise this familiar comment? "Please mention your PostgreSQL version". You know the drill by now. SQL (check), Error message (check), PostgreSQL version (nope), Schema or SQLFiddle (nope), sample data (nope). /frustrated — Craig Ringer, Oct 18 '12 at 10:30
What exactly is stored in the column `test.str`? Can you show some sample data and the expected output? — , Oct 18 '12 at 10:31
@a_horse_with_no_name `test.str` its a string just a string. — Kliver Max, Oct 18 '12 at 10:37
@KliverMax There's no `string` data type. Do you mean `text`? `varchar`? — Craig Ringer, Oct 18 '12 at 10:39
@KliverMax To me it's just considerate to put some effort in yourself, showing that you respect others' time. Of course that's purely my opinion, but it's in line with the site guidelines and FAQ. Also, while you think it's just basic SQL, *you're asking for help with it* so it might not be what you think. Consider reading the "How to ask better questions" section of http://stackoverflow.com/tags/postgresql/info . — Craig Ringer, Oct 18 '12 at 10:41

maniek · Accepted Answer · 2013-10-22T13:10:41.693

13

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

edited Oct 22 '13 at 13:10

answered Oct 18 '12 at 11:11

maniek

7,087
2
20
43

Nice guess at the poster's actual intentions. Will the second formulation with `LIKE ANY (...)` really work? If so, that's *cool*. – Craig Ringer Oct 18 '12 at 11:13
@CraigRinger: Yes, it will. Another nice example : `... where tsvector_col @@ any(select tsquery_col...)` – maniek Oct 18 '12 at 11:20
Sure does. You learn something every day 'round here. Demo: http://sqlfiddle.com/#!12/fc538/1 – Craig Ringer Oct 18 '12 at 11:24

Craig Ringer · Answer 2 · 2012-10-18T11:27:46.917

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

@KliverMax It's OK the first few times, I just think you should know better by now. Please *think about it* before your next question. — Craig Ringer, Oct 18 '12 at 11:26

Craig Ringer · Answer 3 · 2012-10-18T11:26:54.983

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

Subquery returns ~200 results. its mean that i cant find a string using several substrings in Postgre? — Kliver Max, Oct 18 '12 at 10:44

Searching substring in PostgreSQL

3 Answers3

Linked