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