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

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, Adrian Klaver wrote:

test=# select * from like_test where fld_1 ilike '%Winding River%';
fld_1

Adrian,

Aha! I thought of 'like' but forgot about ilike. That's exactly what I need.

Thanks very much,

Rich

On Thu, 29 Aug 2019, Gary Cowell wrote:

Look at the 'LIKE' function

Gary,

Yes, I thought of like but didn't think to look for it in the postgres
manual.

Thank you very much,

Rich

On 8/29/19 8:47 AM, Rich Shepard wrote:

On Thu, 29 Aug 2019, Gary Cowell wrote:

Look at the 'LIKE' function

Gary,

Yes, I thought of like but didn't think to look for it in the postgres
manual.

Thank you very much,

Rich

I've given up on the "likes" in favour of the ~ (tilde) and ~* (tilde
asterisk) operator.  Way cool, powerful. Not standard sql though (iirc)

On Thu, 29 Aug 2019, Rich Shepard wrote:

Aha! I thought of 'like' but forgot about ilike. That's exactly what I
need.

'thought' is the wrong word. I should have written that I once knew of like
and had forgotten it.

Rich