I have a requirement where I need to do case-insensitive joins across the system and I don't wish to apply upper/lower functions. I tried setting TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci') at Table level but still the comparison is happening considering case sensitivity. PSB -
drop table test.caseI; create table test.caseI (name string, id int) TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci'); insert into test.caseI values ('hj',1);
drop table test.caseI_2; create table test.caseI_2 (name string, id int) TBLPROPERTIES('serialization.encoding'='utf8mb4_unicode_ci'); insert into test.caseI_2 values ('HJ',1);
select * from test.caseI i inner join test.caseI_2 i2 on i.name=i2.name; --No Result
Tried with encoding 'SQL_Latin1_General_CP1_CI_AI' but got same result as above.
Any help would be appreciated, thanks!