Обсуждение: Query using 'LIKE' returns empty set

Поиск
Список
Период
Сортировка

Query using 'LIKE' returns empty set

От
Rich Shepard
Дата:
Next problem is one I've not before encountered.

The .sql file used to import data to the fish_counts table has rows such as
this one:

('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to North
Fork Nehalem River','0-3.25','161980','Unknown','Jack or subadult','Peak
live & dead fish','Spawner Counts','ODFW','2012-01-06','1950-1974',25,
'1950-10-01','1951-01-31','Ground','Actual Physical Counts',0), 
[Lines wrapped in the message only.]

When I submit this query I get no rows returned:

select * from fish_counts where stream_tribs ilike 'Nehalem';
  count_id | loc_id | downstream | upstream | stream_name | stream_tribs | r
iver_miles | itis_tsn | production | life_stage | count_type | data_categor
y | compiled_by | updated | years | nbr_observations | begin_date | end_dat
e | sample_method | calc_method | count_value 
----------+--------+------------+----------+-------------+--------------+--
-----------+----------+------------+------------+------------+-------------
--+-------------+---------+-------+------------------+------------+--------
--+---------------+-------------+-------------
(0 rows)

What is equally puzzling is when I search the input file using
     grep -c -e "Nehalem" fish_counts
0 is returned, the same as the postgres query.

I want to understand what I've done incorrectly.

TIA,

Rich

Re: Query using 'LIKE' returns empty set

От
Rob Sargent
Дата:
On 8/29/19 10:39 AM, Rich Shepard wrote:
> Next problem is one I've not before encountered.
>
> The .sql file used to import data to the fish_counts table has rows 
> such as
> this one:
>
> ('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to 
> North
> Fork Nehalem River','0-3.25','161980','Unknown','Jack or subadult','Peak
> live & dead fish','Spawner Counts','ODFW','2012-01-06','1950-1974',25,
> '1950-10-01','1951-01-31','Ground','Actual Physical Counts',0), [Lines 
> wrapped in the message only.]
>
> When I submit this query I get no rows returned:
>
> select * from fish_counts where stream_tribs ilike 'Nehalem';
>  count_id | loc_id | downstream | upstream | stream_name | 
> stream_tribs | r
> iver_miles | itis_tsn | production | life_stage | count_type | 
> data_categor
> y | compiled_by | updated | years | nbr_observations | begin_date | 
> end_dat
> e | sample_method | calc_method | count_value 
> ----------+--------+------------+----------+-------------+--------------+--
> -----------+----------+------------+------------+------------+------------- 
>
> --+-------------+---------+-------+------------------+------------+-------- 
>
> --+---------------+-------------+-------------
> (0 rows)
>
> What is equally puzzling is when I search the input file using
>     grep -c -e "Nehalem" fish_counts
> 0 is returned, the same as the postgres query.
>
> I want to understand what I've done incorrectly.
>
> TIA,
>
> Rich
>
>
Are you sure that particular file has the search string?

grep -i nehalem fish_counts

Re: Query using 'LIKE' returns empty set

От
Michael Lewis
Дата:
You need the wildcard character at front and back.

select * from fish_counts where stream_tribs ilike '%Nehalem%';

Re: Query using 'LIKE' returns empty set [FIXED]

От
Rich Shepard
Дата:
On Thu, 29 Aug 2019, Rob Sargent wrote:

> Are you sure that particular file has the search string?

Rob,

I'm suitably embarrased: that's the wrong file name. I must be seriously
under cafinated. The proper file is fish_data.sql so grep and postgres
return 1409 instances.

My apologies to all,

Rich