Обсуждение: blank in query - cannot return results
Hello, PostgreSQL 8.3.18 on x86_64-linux-gnu, query: select * from table_name where col1 = 'Abc, test'; select * from table_name where col1 ilike '%Abc, test%'; NO result returned. But run: select * from table_name where col1 ilike '%Abc,%test%'; -- remove blank Result is returned. PSQL terminal encoding: unicode (UTF-8). From both terminal and db, values shows/and displayed as "Abc, test". But " " space/blank in where section cannot return query result. Can someone tell why may cause the issue please? Thanks a lot!
> Hello,
>
> PostgreSQL 8.3.18 on x86_64-linux-gnu, query:
>
> select * from table_name where col1 = 'Abc, test';
> select * from table_name where col1 ilike '%Abc, test%';
>
> NO result returned.
>
>
> But run:
> select * from table_name where col1 ilike '%Abc,%test%'; -- remove
> blank
>
> Result is returned.
>
>
>
> PSQL terminal encoding: unicode (UTF-8). From both terminal and db,
> values shows/and displayed as "Abc, test".
>
> But " " space/blank in where section cannot return query result.
One more comments, for all non letter/numbers such as "-, ." in the
query won't return any results.
>
> Can someone tell why may cause the issue please?
>
> Thanks a lot!
On 10/08/2015 12:51 PM, Emi wrote: > Hello, > > PostgreSQL 8.3.18 on x86_64-linux-gnu, query: > > select * from table_name where col1 = 'Abc, test'; > select * from table_name where col1 ilike '%Abc, test%'; > > NO result returned. Well without an indication of what the value in col1 is this is not going anywhere. My suspicion is you are looking for regex: http://www.postgresql.org/docs/9.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP > > > But run: > select * from table_name where col1 ilike '%Abc,%test%'; -- remove blank > > Result is returned. > > > > PSQL terminal encoding: unicode (UTF-8). From both terminal and db, > values shows/and displayed as "Abc, test". > > But " " space/blank in where section cannot return query result. > > Can someone tell why may cause the issue please? > > Thanks a lot! > > -- Adrian Klaver adrian.klaver@aklaver.com
But run:
select * from table_name where col1 ilike '%Abc,%test%'; -- remove blank
Result is returned.
It works.
WITH vals (v) AS ( VALUES ('Abc, test'::text) )
SELECT *
FROM vals
WHERE v ILIKE '%Abc, test%'
If you are expecting a match and are not getting one that you need to look at your data and figure out where you misunderstanding lies.
David J
P.S.
"
a percent sign (%) matches any sequence of zero or more characters"
Removing the space and putting a "%" in in place simply means that the space was optional, and since it now matches, is not present in the values that appear in this query but not the one with an explicit space.
Note that there are different kinds of spaces...