Why two string do not match although they are exactly the same?

user_16486116 0
Viewed 79

I have a lookup table with a list of values. Lookup Table I need to filter on a value from the LUT table in a simple where condition. It works with all table values except one and I don't know why. I have tried using trim function and lower function to change the string but nothing helped. Does anyone have the same experience? Why it does work for all table values except one? My code: SELECT * FROM "PossibleNewGMCIssues" WHERE "gmcIssue" = 'Suspended account for policy violation'

Thank you in advance.

snowflake-cloud-data-platform
1 Answers

It's too hard for anybody to say without seeing the strings themselves. They probably look similar but have different unicode values. You can convert to the hex values to see where they are different though by using hex_encode:

Below I create a table that uses two strings that look the same but aren't. One contains an m-dash and the other an en-dash.

-- Create a table with two columns with strings in them that look the same but arent
create or replace transient table test_table as (
    select 'a-string'::string col1, 'a—string'::string col2
);

-- This returns 0 results
select * from test_table where col1=col2;

-- You can tell that the strings are different by checking the hex representation of them
select hex_encode(col1), hex_encode(col2)
from test_table
;

-- The above returns:
-- +----------------+--------------------+
-- |HEX_ENCODE(COL1)|HEX_ENCODE(COL2)    |
-- +----------------+--------------------+
-- |612D737472696E67|61E28094737472696E67|
-- +----------------+--------------------+
user_32872500
Related