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@xxxxxxxxxxx
