Thread: find a substring on a text (data type) column

I was trying to find a substring on a text (data type) column like 'cat foo dog ...'.
I use the query below
SELECT id FROM table WHERE name LIKE '% foo %';
Sometimes the query return with nTuples=0 but there are matching rows.
On retry, the query return with expected results. Any ideas;

(postgres ver 9.2, libpq - C Library)

Hello

2012/11/8 pantelis vlachos <vlachos83@gmail.com>:
> I was trying to find a substring on a text (data type) column like 'cat foo
> dog ...'.
> I use the query below
> SELECT id FROM table WHERE name LIKE '% foo %';
> Sometimes the query return with nTuples=0 but there are matching rows.
> On retry, the query return with expected results. Any ideas;

isn't problem in spaces, so LIKE '% foo %' must fail when symbol is on
begin or end?

you can check ' ' || name || ' ' LIKE '% foo %'

or better - use fulltext instead

Regards

Pavel Stehule

>
> (postgres ver 9.2, libpq - C Library)