Somehow I like Snowflake more and more

It’s no secret that I like Snowflake. When I say like, I mean that I like some of the different ways that I can find data within other data. For example, see this post from last time.

This time, I want to show why one of the many operators are used over another and what makes it work successfully.

Like

Like is just like the operator “like” in SQL Server, if all of your data is case sensitive.

To see this well, we can add a new person into our testing database that will give most of us conniption fits.

INSERT INTO TESTING
(FirstName, LastName, Age, DOB, DOD)
SELECT 'Josh%a', 'Smythe', 21, '2001-03-12', NULL;

Notice the % in the first name. In Snowflake, we can find this name pretty simply with the following:

And yes, you can do the same thing in SQL Server. How many of you have used that functionality? Somehow I’m thinking the number is low…

iLike

Ilike is like’s case-insensitive sibling. Everything works the same way, even the ESCAPE sequence shown above.

For whatever reason, ilike is much harder for me to type for some reason – I always type it as ilkie. Yes, I have it set to autocorrect for this very reason. If you find it hard to type consistently, I suggest doing the same.

RLIKE

RLike is where you can have some fun with regex. Yes, I’m aware not too many people ever put those two words – “fun” and “regex” – together in a sentence, but bear with me.

Notice in the above that I actually use rlike 2 different ways. One as a function – shown in the second line – and one as an operator – shown in the first. Pick whichever makes the most sense to you as they both work equally well.

LIKE ALL or LIKE ANY

Have you ever had a case where you want to get data that meets 2 or more criteria? Or if it meets any of a set of criteria? Well, wait no more! With “LIKE ALL” I can get data if it meets every one of a set of criteria – but it does have to be case sensitive. With “LIKE ANY”, as long as it meets one of the criteria I have listed, it’s good to go!

CONTAINS

If after all of those options, you still don’t find data you can use, let’s use the CONTAINS operator.

There are two things to be aware of with the CONTAINS operator.

1. It will only give you a true or false. If you were to use it in a WHERE clause, remember to have “= TRUE” with it, as follows:

And with that my friends, I have come to the conclusion of today’s post. There are lots of ways to find data that is like a pattern. In Snowflake, just be sure to use the correct way to find it or you may just miss out…