Re: Selecting rows having substring in a column

Look at the 'LIKE' function

select * from Fishes where stream_trib like '%Winding River%';

You need to 'bookend' your string with '%' for 'LIKE' function, if the
string could match anywhere. If it could only be at the end, you could
use '%Winding River'

If case is an issue, wrap it with upper (or lower)

.... where upper(stream_trib) like '%WINDING RIVER%';

You could also use a regular expression, or 'SIMILAR', but 'LIKE' is
often the simplest.

More information here:

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

On Thu, 29 Aug 2019 at 15:13, Rich Shepard <rshepard(at)appl-ecosys(dot)com> 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.
>
> Regards,
>
> Rich
>
>