Whenever I say the keyword INSERT in conjunction with databases, we – as data developers – automatically think that we’re going to be putting data into a table (or multiple tables with Snowflake – see here). With Snowflake, that isn’t always true.
Now, you’re probably saying to yourself “Sherpa has obviously lost his/her mind. Who would use INSERT for anything except inserting data into a table? I’ve heard of overloading objects in C#, but that’s ridiculous.”
In Snowflake, we can use our new function – INSERT – to insert some data into our strings. The INSERT function consists of four parts:
- The string we’re working with
- The position we’re inserting characters at
- This is 1-based, not 0-based.
- The number of characters we’re replacing
- The string to be inserted.
In our table that we’ve been working a lot with recently – public.testing — we have 2 names to work with – Fred and Barney.
We can now take the data, insert a string into this data, delete data out of it if we need to, and show it again. Let’s see that magic happen:
Notice that “Fred” even with just 4 letters, can magically have something shoved into the 5th position. And we got rid of those 2 pesky “ey” characters in Barney to make way for our new string.
Also, it is worth mentioning that we don’t have to actually delete data with this function. We just put 0 in the 3rd parameter. We can actually use other functions to determine things like the length of our original string to populate the data as we desire.
We can also use the INSERT function to only delete data, not insert any. Notice that the string we’re inserting is now a blank string.
And the final fun item to be aware of is that if any of these 4 parameters is NULL, then the outcome will automatically be NULL.
So, if you ever need to shove data into another string – if you know the exact position that you want to shove it into – then the INSERT function can be your friend! Unless you are actually trying to INSERT data into another table – that might be one too many INSERTs for one query to sustain (And probably too much for multiple developers too.)
With that, I hope you have learned something this week that can be useful going forward. But, at least, you’ll remember that INSERTing with INSERTs is going to be a mess to troubleshoot.