Does Snowflake support case insensitive where clause filter similar to SQL Server

Question

I am migrating SQL code to snowflake and during migration i found that by default snowflake is comparing varchar field (ex. select 1 where 'Hello' = 'hello') incorrectly. To solve this problem i set collation 'en-ci' at account level. However now i am not able to use REPLACE like crucial function.

Is it possible in snowflake to do case insensitive varchar comparison (without mentioning collation explicitly or using UPPER function every time) and still use replace function?

I will appreciate any help.

Thanks,

Answer 1

regexp_replace is your friend, it allows for 'i' parameter that stands for "ignore case": https://docs.snowflake.com/en/sql-reference/functions/regexp_replace.html

For example you can do something like that:

select regexp_replace('cats are grey, cAts are Cats','cats','dogs',1,0,'i');

I've assumed the default values for position and occurrence but those can also be adjusted

And you can still do comparison (also based on regexp, aka "RLIKE"): https://docs.snowflake.com/en/sql-reference/functions/rlike.html

Answer 2

you can compare the test with a case intensive match via ILIKE

select 1 where 'Hello' ilike 'hello'