Обсуждение: find a substring on a text (data type) column

Поиск
Список
Период
Сортировка

find a substring on a text (data type) column

От
pantelis vlachos
Дата:
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)

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

От
Pavel Stehule
Дата:
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)