With SQL Server, you’ll find that, quite often, when dealing with strings, you’re almost stuck using the like operator to check if items are “like” another string. I don’t know how many times I’ve done something like the following:
SELECT * FROM dbo.TableName WHERE Field LIKE '%Name%';Hopefully, many of you get the gist of the above. We’re wanting anything that shows in the Field column with the string “Name” somewhere in it.
In Snowflake, there is a like operator, but it doesn’t always work the same way. First, we’ll create our table and load some data into it the same way we did last time.
INSERT INTO TESTING
(FirstName, LastName, Age, DOB, DOD)
SELECT 'Sherpa', 'Data', 30, '1990-07-16', NULL UNION
SELECT 'Fred', 'Flintstone', 100, '1922-04-03', '2022-12-6' UNION
SELECT 'Barney', 'Rubble', 94, '1926-03-12', NULL ;And then we run our query against the data:
But now, we change it from st to ST and what happens?
Yes, that’s right the like operator is case-sensitive. Now, there is another operator that, surprise-surprise, is case-insensitive.
And just like magic – with the ilike, things are back to working like we’re used to.
Now, if you want to do anything with regular expressions – and, sadly, no matter how much I try to get away from them, I’m pulled back into using them – you’ll need to use yet another operator – the rlike operator.
Using the ilike in this scenario instead of the rlike will return you 0 rows.
And that, my friends, is all we have time for this post. Until next time my friends!