Re: Selecting rows having substring in a column

On 8/29/19 7:13 AM, Rich Shepard wrote:
> Using postgres-11.1 here. My SQL knowledge needs expanding and my web
> searches have not found a satisfactory answer. I hope to learn the correct
> approach here.
>
> A table (Fishes) has an attribute column stream_trib with values such as
>     Small Creek trib to Winding River
>     Roaring River trib to Winding River
> and I want to find all rows containing Winding River in that column.
>
> The postgres substring function takes as arguments the substring, starting
> position, and length. In my table the staring position varies although the
> length remains constant.
>
> I need to learn how to construct a SELECT statement that returns the set of
> rows containing the substring 'Winding River'. A pointer to references
> would
> be great; so would a detailed lesson in handling this and similar queries.

https://www.postgresql.org/docs/11/functions-matching.html

create table like_test(fld_1 varchar);

insert into like_test values ('Small Creek trib to Winding River');
insert into like_test values ('Roaring River trib to Winding River');
insert into like_test values ('Roaring River');

test=# select * from like_test where fld_1 ilike '%Winding River%';
fld_1
-------------------------------------
Small Creek trib to Winding River
Roaring River trib to Winding River
(2 rows)

Using ilike to case-insensitive search.

If you want more in depth search:

https://www.postgresql.org/docs/11/functions-textsearch.html

> > Regards,
>
> Rich
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com