Categories: Snowflake

Cool Stuff in Snowflake – Part 9: ILIKE

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.

Today I have a small blog post about a neat little function I discovered last week – with thanks to my German colleague, who wants to remain anonymous. The function is called ILIKE and it is syntactic sugar for the combination of UPPER and LIKE. Let’s try it out. First we create a small table with some sample data:

Next we want to return all strings starting with “Hello”, so we whip out the LIKE function:

Alas, it returns only one row, because Snowflake is case-sensitive by default. What a treat. Normally you would use UPPER on the column (which is a bad idea in SQL Server, because then no index can be used), but here we can just use the ILIKE function:

Hooray! All three rows returned. ILIKE this function! (Sorry)

------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.