snowflake-cloud-data-platform

Select+where query is not returning data, but select-all + search option is returning data - why and how to solve this?


Here is a demo from the issue.

When i using below sql to fetch specific data, i get nothing.

select * from tmp where ord ='904000009‬‬‬' or trim(ord) ='904000009‬‬‬';

![SELECT

But then when i am using select * and search functionality, I can see the data. This is a text column and i tried using trim to remove spaces but it still not working.

select * from tmp; ![![Using Search Option

I tried many variations like below but none worked.

select ord, * exclude ord from tmp where ord ='904000009‬‬‬' or trim(ord) ='904000009‬‬‬' ; select * from tmp where '00'||ord ='00904000009‬‬‬' ;

As per request, here is the SQL that shows only good row and doesnt show bad row.

with t as (select  '4000009616‬‬‬' c, 'good' indicator union all select  '4000009616' as c , 'bad' indicator)
select * from t
where c='4000009616‬‬‬';

enter image description here


Solution

  • There are some invisible characters (Pop Directional Formatting U+202C) involved:

    select * from tmp where ord  ='904000009‬‬‬' or trim(ord) ='904000009‬‬‬';
    

    enter image description here

    enter image description here

    I suggest removing them from the query:

    select * from tmp where ord  ='904000009' or trim(ord) ='904000009';