Substring, also known as substr, is a string and binary function in Snowflake, in its simplest form all you need to run is
select substring(column,start,length) from table. In this post I will walk through what it’s useful for and how to use it in your database.
Snowflake substring, what is it?
Substring is a Snowflake function which takes a column and reduces each value to a subset of the original, based on the start index and length defined. It is similar to the functions
right and is also known as splitting, slicing, or cutting.
How do you use substring in Snowflake?
Using substring in Snowflake is easy once you get the hang of it. Firstly, let’s look at the syntax of the function:
substring( base_expr , start_expr , length_expr )
The parameters for the function are:
base_expr- The column or value which will be split
start_expr- The index position where the splitting will start
length_expr- The number of characters to return after
There are a few things that you should be aware of when using this function:
- The first index value of a string is 1, not 0
base_exprmust either be a binary or a string, and the output value is the same type as this input
start_expris negative then this is the index in relation to the end of the
length_exprmust be greater than 0, otherwise an empty string is returned
length_expris not a required field. If it is blank then all characters after
nullis returned if any of the inputs are
Example of using substring function in Snowflake
Substring is a very helpful function to know as it is a catch all for slicing column values. To demonstrate how this function could be used in a real use case, let’s look at an example where you want to return different sections of a phone number:
/* | phone_number | |--------------| | 123456789 | | 987654321 | */ select phone_number, substring(phone_number, 1, 3) as first_3_digits, substring(phone_number, 4, 3) as middle_3_digits, substring(phone_number, -3, 3) as last_3_digits, substring(phone_number, -6) as last_6_digits from phone_number /* | phone_number | first_3_digits | middle_3_digits | last_3_digits | last_6_digits | |--------------|----------------|-----------------|---------------|---------------| | 123456789 | 123 | 456 | 789 | 456789 | | 987654321 | 987 | 654 | 321 | 654321 | */