ILIKE() function in Snowflake ?

Explained: ILIKE() function in Snowflake ?

by

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

What is ILIKE() Function?

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

How to use ILIKE() Function in Snowflake?

ILIKE() function is used to allow the matching of strings based on comparison with a pattern. Unlike the LIKE() function, string matching is case-insensitive. 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 ILIKE Function in Snowflake?

-- Syntax : 

Input_column ILIKE Pattern [ ESCAPE ]

ILIKE() Argument Details :

Input_columnString  value ( Mandatory )
PatternString value ( Mandatory )
ESCAPECharacter value ( Optional )

Input_column:

Input that needs to match.

Pattern :

The pattern that needs to match.

Escape :

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

Examples of ILIKE() 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 ILIKE() function 

select * from demo.public.emp
where name ilike 'RA%'
order by name;

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


select * from demo.public.emp
where name ilike 'RA_%'
order by name;

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

Examples for making use of ILIKE() function with Escape

select * from demo.public.emp
where name ilike 'sa^m_%' escape '^'
order by name;

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

As we can see from the above examples we got the output that matches the pattern. Without an escape, we got two values. With escape, we got one value because here ‘^’ is escaped from the pattern. 

Full Example of ILIKE() 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 ILIKE function to table data

SELECT * from emp
where name ilike 'RA_%'
order by id;
+----+-----------+----------+
| ID | NAME      | INFO     |
|----+-----------+----------|
|  1 | Ram       | NOT-NULL |
|  2 | RAJU      | NOT-NULL |
|  7 | Rajiv Ram | NOT-NULL |
+----+-----------+----------+

When you should use ILIKE() Function in Snowflake?

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

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

Real World Use Case Scenarios for ILIKE Function in Snowflake

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

ILIKE() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about ILIKE() function and 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 – 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