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 `left`

and `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`start_expr`

**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
- The
`base_expr`

must either be a binary or a string, and the output value is the same type as this input - If
`start_expr`

is negative then this is the index in relation to the end of the`base_expr`

`length_expr`

must be greater than 0, otherwise an empty string is returned`length_expr`

is not a required field. If it is blank then all characters after`start_expr`

are returned`null`

is returned if any of the inputs are`null`

## 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 |
*/
```

## Related articles

Snowflake coalesce function

Snowflake replace function

Snowflake listagg function

Snowflake concat function