Thread: Selecting rows having substring in a column

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

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 at 15:13, Rich Shepard <rshepard@appl-ecosys.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
>
>

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