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.klaver@aklaver.com