SUBSTRING() function in Snowflake ?

Explained: SUBSTRING() function in the Snowflake?

by

Are you looking to find how to use the SUBSTRING() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the SUBSTRING function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use the SUBSTRING() function. I will explain the SUBSTRING() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the SUBSTRING() expression.

What is SUBSTRING() Function in Snowflake?

SUBSTRING() function helps to get the substring from a string by providing the starting index and length of the substring. For example, if we want to get the name ‘John’ from the name ‘John Rose’, then we can make use of this function as: substring(‘John Rose’,0,4).

How can we use SUBSTRING() Function within Snowflake?

SUBSTRING() function returns the position of the string or binary value from the full string, starting from the character specified by substring_start_index. If any of the inputs are NULL then NULL will be returned.

Syntax for Substring function in Snowflake

-- Syntax : 

SUBSTRING ( full_string, substring_start_index, length_of substring )

SUBSTRING() Parameters Details:

Base_ExpressionString or Binary value ( Mandatory )
Start_ExpressionInteger value ( Mandatory )
LengthInteger value ( Mandatory )
Table 1: Substring Parameter Details

Base_Expression :

A String or Binary expression that we can make use of to get the substring.

Start_Expression :

Start Expression evaluates to be an integer that represents starting position. It specifies from where the substring needs to be started. Offset measurements are given below :

  • The number of characters if the input is Varchar.
  • The number of bytes if the input is Binary

Length :

Length expression evaluates to be an integer that specifies the length required from the start expression. It should specify :

  • The number of characters if the input is Varchar.
  • The number of bytes if the input is Binary

Returns :

The data type of the returned value is the same as the input data type.

Example:

Example to get the substring from a specific string in Snowflake

select substr('testing 1 2 3 4', 9, 7);

+---------------------------------+
| SUBSTR('TESTING 1 2 3 4', 9, 7) |
|---------------------------------|
| 1 2 3 4                         |
+---------------------------------+

Example to get the substring from a specific string by using table data.

select ID,name,substring(name,0,2) from emp;

+----+-------------+---------------------+
| ID | NAME        | SUBSTRING(NAME,0,2) |
|----+-------------+---------------------|
|  1 | Ram         | Ra                  |
|  2 | RAJU        | RA                  |
|  3 | Sameer      | Sa                  |
|  4 | Sai Kumar   | Sa                  |
|  5 | Naveen Sai  | Na                  |
|  6 | Seshi Kumar | Se                  |
|  7 | Rajiv Ram   | Ra                  |
+----+-------------+---------------------+

Full Example of substring() function in Snowflake.

-- Create a Table

create or replace TABLE EMP (
	ID NUMBER(38,0),
	NAME VARCHAR(30) COMMENT 'NAME OF THE EMPLOYEE',
	INFO VARCHAR(20) DEFAULT 'NOT-NULL'
);

-- Insert Data

insert into emp(id,name) values 
(1,'Ram'),
(2,'RAJU'),
(3,'Sameer'),
(4,'Sai Kumar'),
(5,'Naveen Sai'),
(6,'Seshi Kumar'),
(7,'Rajiv Ram'),
(8,NULL)

-- Apply substring function

select id,name,substring(name,0,4) from emp;

-- Output

+----+-------------+---------------------+
| ID | NAME        | SUBSTRING(NAME,0,4) |
|----+-------------+---------------------|
|  1 | Ram         | Ram                 |
|  2 | RAJU        | RAJU                |
|  3 | Sameer      | Same                |
|  4 | Sai Kumar   | Sai                 |
|  5 | Naveen Sai  | Nave                |
|  6 | Seshi Kumar | Sesh                |
|  7 | Rajiv Ram   | Raji                |
|  8 | NULL        | NULL                |
+----+-------------+---------------------+

When you should use SUBSTRING Function in Snowflake?

There are certain use case scenarios when it is recommended to use the substring function within the Snowflake cloud data warehouse which are as follows:

  • If we want to get a specific partial string from an expression or string.
  • In case if you want to get the substring from a string that too in the reverse order then also you can use the substring function. For example:
select id,name,substring(name,-2,2) from emp;

+----+-------------+----------------------+
| ID | NAME        | SUBSTRING(NAME,-2,2) |
|----+-------------+----------------------|
|  1 | Ram         | am                   |
|  2 | RAJU        | JU                   |
|  3 | Sameer      | er                   |
|  4 | Sai Kumar   | ar                   |
|  5 | Naveen Sai  | ai                   |
|  6 | Seshi Kumar | ar                   |
|  7 | Rajiv Ram   | am                   |
|  8 | NULL        | NULL                 |
+----+-------------+----------------------+

Whenever you want the substring in the reverse order then use the start index as the negative value.

Real World Use Case Scenarios for SUBSTRING Function in Snowflake

  • If we want to get the lastname, or surname from the complete name of the customers.
  • We want to get the list of country codes from the mobile number.

SUBSTRING Snowflake Official Documentation Link

Is substring allowed in Snowflake?

Yes substring is allowed within the Snowflake. We can get the substring out of any string or column using the substring function given by Snowflake.

Final Thoughts

In this article, we have learned about SUBSTRING() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar