Explained: STARTSWITH() function in Snowflake?

Are you looking to find how to use the startswith() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the startswith 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 startswith() function. I will explain the startswith() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the startswith() expression.

What is STARTSWITH() Function?

STARTSWITH function helps to check the starting of expression with column values. For example, if we want the names starting with ‘R’ then we will get the list of all the names that are starting with the particular character.

How can we use STARTSWITH() Function?

STARTSWITH() function returns true if expression-1 STARTSWITH with expression-2. Both the expressions must be text or binary expressions.

What is the syntax of startswith() function?

-- Syntax : 

STARTSWITH	( Expression-1, Expression-2)

STARTSWITH() Argument Details :

Expression-1String or Binary value ( Mandatory )
Expression-2String or Binary value ( Mandatory )
Table 1: StartsWith function in Snowflake Argument List

Expression-1 :

A String or Binary expression to search in.

Expression-2 :

A String or Binary expression to search for at the starting of the expression-1.

Returns :

The data type returns a Boolean value. The value will be True if expression-1 starts with expression-2. Returns NULL if either input expression is NULL. Otherwise, it returns False.

Examples of StartsWith() function in Snowflake:

select id, name from DEMO.PUBLIC.EMP;

+----+--------+
| ID | NAME   |
|----+--------|
|  1 | Ram    |
|  2 | RAJU   |
|  3 | Sameer |
+----+--------+

select * from DEMO.PUBLIC.EMP where startswith(name, 'Ra');

+----+------+----------+
| ID | NAME | INFO     |
|----+------+----------|
|  1 | Ram  | NOT-NULL |
+----+------+----------+

As we can see from the above example, we got only the value ‘Ram’ but not ‘RAJU’ because this function is case-sensitive.

Full example of STARTWITH() function in Snowflake.

-- Create a table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
);

-- Insert Data

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


-- Table Data

SELECT * from emp;
+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  1 | Ram         | NOT-NULL |
|  2 | RAJU        | NOT-NULL |
|  3 | Sameer      | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  5 | Naveen Sai  | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
|  7 | Rajiv Ram   | NOT-NULL |
|  8 | NULL        | NOT-NULL |
+----+-------------+----------+

-- Applying STARTSWITH to the table data

select * from EMP where startswith(name, 'Ra');
+----+-----------+----------+
| ID | NAME      | INFO     |
|----+-----------+----------|
|  1 | Ram       | NOT-NULL |
|  7 | Rajiv Ram | NOT-NULL |
+----+-----------+----------+

When you should use STARTSWITH() Function in Snowflake?

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

  • If you want to get the list of data that are starting with particular expression.
  • If you want to get the list of databases, tables that are strating with a particluar expression.

Real World Use Case Scenarios for STARTSWITH Function in Snowflake

  • We can get the list of customer’s start with ‘N’ as lastname.
  • We can also get the location of bank branches that are starting with ‘R'( location starting with R ).

STARTSWITH() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about STARTSWITH() 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.

Leave a Comment