Are you looking to find what is ILIKE ANY() function is within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the ILike Any 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 ILIKE ANY() function. I will explain the ILIKE ANY() function by taking practical examples. So don’t waste time let’s start step by step guide to understanding what is the ILIKE ANY() expression and its uses.
Contents
What is ILIKE ANY Function in Snowflake?
ILike Any function is used to match the column with multiple 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 ANY Function in Snowflake?
ILIKE ANY() function is used to allow matching of strings based on comparison with the given patterns. Unlike, the ILIKE ANY() function, string matching is case-insensitive. LIKE ANY, ILIKE ANY and RLIKE ANY all perform similar operations. However, RLIKE ANY uses POSIX EXE syntax instead of SQL Pattern used by LIKE ANY and ILIKE ANY.
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 ANY Function in Snowflake?
-- Syntax :
input_column ILIKE ANY ( Pattern-1, Pattern-2,..) [ Escape ]
ILIKE ANY() Argument Details :
| input_column | String value ( Mandatory ) |
| Pattern | String value ( Mandatory at least one) |
| ESCAPE | Character value ( Optional ) |
input_column:
It is the input column that is getting compared with the patterns. Typically a varchar, although some other datatypes can be used.
Pattern :
Patterns that the string is to be compared to. Typically a varchar, although some other datatypes can be used. We must specify at least one pattern.
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.
Return :
The data type of returned value is true/false.
Example of ILIKE ANY in Snowflake:
First let’s create a table and insert some sample data in it.
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 to use ILIKE ANY() function
select * from demo.public.emp
where name ilike any ('Ram%', '%S%')
order by name;
+----+--------+----------+
| ID | NAME | INFO |
|----+--------+----------|
| 1 | Ram | NOT-NULL |
| 3 | Sameer | NOT-NULL |
+----+--------+----------+
select * from demo.public.emp
where name ilike any ('RAJ%', '%S%')
order by name;
+----+--------+----------+
| ID | NAME | INFO |
|----+--------+----------|
| 2 | RAJU | NOT-NULL |
| 3 | Sameer | NOT-NULL |
+----+--------+----------+
As we can see the above examples we got the output that matches the pattern. The advantage is here we can compare more than one pattern as well. So, we got the output which satisfies any of the patterns.
When you should use ILIKE Any Function in Snowflake?
There are certain use case scenarios when it is recommended to use the ILike any function within the Snowflake cloud data warehouse which are as follows:
- You want to match the column value on more than any one pattern.
- You want to match the input using SQL based wildcards, for example : %, _.
Real World Use Case Scenarios for ILIKE ANY Function in Snowflake
- You wanted fetch all the customer living in any of the area among the set of the area.
- In the banking application you wanted to fetch all the account which contains any codes like ‘NPS’, ‘LOAN’ or ‘CUR’.
ILIKE ANY Snowflake Official Documentation Link
Final Thoughts
In this article we have learned about ILIKE ANY() function and uses with the examples explained clearly. I have also covered different scenario with practical example that has helped you to understand it holistically. 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.
- For Azure Study material Join Telegram group : Telegram group link:
- Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
- Azure Tutorial Videos: Videos Link
- Date_Trunc() in Snowflake
- Date_Add() in Snowflake
- Parse_Json() in Snowflake
- Insert() in Snowflake
- Right() in Snowflake
- StartsWith() in Snowflake
- Replace () in Snowflake
- Position() function
- ListAgg() function
- Like() Function
- LikeAll() Function
- Like Any() Function
- Current_Date() Function
- Row_Number() Function
- Cast() Function
- Show() Function
- ISNULL() Function
- Lag() Function
- ILIKE() Function
- DateDiff() Function
- Substring() Function
- ILIKE ANY() Function
- Round() Function
- To_Date() Function
- Concat() Function
- Length() Function
- TRIM(), LTRIM(), RTRIM()
- SPLIT() Function
- Reverse() Function
- Repeat() Function
- Unicode() Function
- IFNULL() Function
- Pivot() Function
- IFF() Function
