Explained: LIKE() function in Snowflake ?

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

What is LIKE() Function ?

Like() function is used to match the column with single patterns in a case sensitive manner. If the column value matches with the input pattern it will return the true otherwise false.

How to use LIKE() Function ?

LIKE() function is used to allow matching of strings based on comparison with a pattern. String matching is case-sensitive. LIKE, ILIKE, and RLIKE all perform similar operations. However, RLIKE uses POSIX EXE syntax instead of SQL Pattern used by ILIKE and LIKE.

What are the SQL wildcards are supported in pattern :

  • Underscore (_) matches any single character.
  • Percent sign (%) matches any sequence of zero or more characters.
  • Wildcards in pattern include newline characters (\n) in subject as matches.
  • There is no default escape character.

What is the Syntax for Like() Function in Snowflake?

-- Syntax : 

Input_column LIKE Pattern [ ESCAPE ]

LIKE() Argument Details :

Input_columnString  value ( Mandatory )
PatternString value ( Mandatory )
ESCAPECharacter value ( Optional )
Table 1: Like in Snowflake Parameter List

Input_column :

Input_column that needs to match. Typically varchar, although some other datatypes can be used.

Pattern :

The pattern that needs to match. Typically varchar, although some other datatypes can be used.

Escape :

The characters that are inserted in front of the wildcard characters indicate that the wildcard should be interpreted as a regular character and not as a wildcard.

Return :

The data type of returned value should be the same as the data type of the subject.

Examples of LIKE() function in Snowflake:

Firstly, let’s create a table and insert some sample data.

Create a table and Insert the data

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


Insert into demo.public.emp(id,name) values((1,'Ram'),(2,'RAJU'),(3,'Sameer'))


select * from demo.public.emp;

+----+--------+----------+
| ID | NAME   | INFO     |
|----+--------+----------|
|  1 | Ram    | NOT-NULL |
|  2 | RAJU   | NOT-NULL |
|  3 | Sameer | NOT-NULL |
+----+--------+----------+

Examples for making use of LIKE() function

select * from demo.public.emp where name like '%R%';

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


select * from demo.public.emp where name like '%Ra%';

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

As we can see from the above examples we got the output that matches the pattern.

Examples for making use of NOT LIKE() Function

select * from demo.public.emp where name not like '%R%';

+----+--------+----------+
| ID | NAME   | INFO     |
|----+--------+----------|
|  3 | Sameer | NOT-NULL |
+----+--------+----------+

select * from demo.public.emp where name not like '%Ra%';

+----+--------+----------+
| ID | NAME   | INFO     |
|----+--------+----------|
|  2 | RAJU   | NOT-NULL |
|  3 | Sameer | NOT-NULL |
+----+--------+----------+

Full Example of LIKE 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 Like Operation

select * from emp where name like '%R%';
+----+-----------+----------+
| ID | NAME      | INFO     |
|----+-----------+----------|
|  1 | Ram       | NOT-NULL |
|  2 | RAJU      | NOT-NULL |
|  7 | Rajiv Ram | NOT-NULL |
+----+-----------+----------+

-- Applying Not Like Operation

select * from emp where name not like '%R%';
+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  3 | Sameer      | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  5 | Naveen Sai  | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
+----+-------------+----------+

When you should use LIKE() Function in Snowflake?

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

  • You want to match the column value on only one pattern.
  • You want to match the input using SQL based wildcards, for example : %, _.

Real World Use Case Scenarios for LIKE Function in Snowflake

  • You wanted to fetch all the customers living in same area.
  • In the banking application, you wanted to fetch all the accounts from a specific branch.

LIKE() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about the LIKE() function and its uses with the examples explained clearly. I have also covered different scenarios with practical examples 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