When you’re working with a database, it’s very hard to not deal with strings at some point in your journey. There are lots of different functions that you will be working with when you’re working with strings. Today, I want to go over some of the basic ones that you’ll use in Snowflake.
The first two that you’ll deal with make the string either upper or lowercase. Yes, that’s right – you’ve probably figured out the names of the functions already. UPPER() and LOWER() are the 2 functions respectively.
Today – for all of our examples – we’ll be using the first line of the song “Look at Miss Ohio” by Gillian Welch.
Both of these can be helpful when making our strings consistent looking. This can be great for reports or when you’re actually comparing strings.
Now I want to stress the last part of that sentence. It is truly great for comparing strings since Snowflake, by default, is case-sensitive. That’s very different than SQL Server for those of you playing at home.
For those of you that normally play with keywords “like”, “ilike”, or even “rlike”, then you’ll need to remember this point. I even have a previous post – found here – where I talk about these functions and case sensitivity.
There’s also – just to add to the mix – the function INITCAP – used to initialize the first letter of every “word” in a phrase. It’s used in Oracle, MySQL, Postgres, and several others. Notice I didn’t say SQL Server in that sentence.
I’ve seen several ways to do it in SQL Server. Some of the older methods use a UDF to make it happen. I’ve seen a few of the more up-to-date methods use the STUFF keyword to make it happen. I’ve seen a few that do other things, but – on the whole – they just don’t work like they claim to. But none of them are really built-in.
Snowflake uses the INITCAP function just like LOWER and UPPER to have the first letter capitalized and everything else lowercase.
Now, please note that – in my example – the phrase “At” is capitalized. The same could be said for “The” if the string had it. Normally, they’re not capitalized in everyday English. I remember at least one of my teachers in high school telling me that “The” should never be capitalized except for at the start of a sentence – though today, that could be totally different.
Of these three functions, the INITCAP function actually has an optional parameter (rarely used, but still there). It’s for when you have a different delimiter to use.
Yes, that’s right my friends. When you want to use something besides the following characters:
<whitespace> ! ? @ ” ^ # $ & ~ _ , . : ; + – * % / | \ [ ] ( ) { } < >
Now, I don’t know about you, but I find that list works fairly well in normal workings. Does it always though? Nope – so Snowflake thought ahead (unlike some of the other databases) and added a parameter for just that specific edge case:
Here, I set the delimiter to be the letter “o”.
Notice that the list of delimiters is now not cumulative. It is truly only the character “o” in this case. Heck, notice that it’s not even the capital “O” – just the lowercase “o”.
Hopefully, one day this could be useful for you. Hopefully. Until then, just keep it in the back of your mind along with your locker combination from 9th grade and your parent’s phone number from back in the day. It could be useful… Maybe…
And with that, I hope everyone is having a great week and we’ll see you soon with yet another post to help you with Snowflake, SQL Server, or something just as fun and exhilarating.