LIKE ANY() function in Snowflake ?

Explained: LIKE ANY() function in Snowflake ?

by

Are you looking to find what is LIKE ANY() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the Like 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 LIcomprar fatos de treino adidas baratos sbloccare oblo lavatrice ariston detske lyžiarske nohavice 134 140 kilpi predam brandon aiyuk youth jersey callaway reva femme jayden daniels jersey hängemattengestell preisvergleich estiti eleganti max mara callaway reva femme nike calças de treino comprar fatos de treino adidas baratos meia com pompom yeezy sneakers kallax korkekiilto hylly blogspot archivador cajonera carpetas colgantesKE ANY() function. I will explain the LIKE ANY() function by taking practical examples. So don’t waste time let’s start step by step guide to understanding what is the LIKE ANY() expression.חליפות מידות גדולות נשים onlysxm.com onlysxm.com propiedadesenrepublicadominicana.com onlysxm.com mindfulmusclellc.com onlysxm.com propiedadesenrepublicadominicana.com automatický dávkovač mýdla lidl lepetitartichaut.com onlinebijuta.com janwoodharrisart.com jorgensenfarmsinc.com bežecké tenisky lepetitartichaut.com

What is LIKE ANY() Function ?

Like Any function is used to match the column with any of the given patterns in a case-sensitive manner. If the column value matches with any of the input patterns, it will return the true otherwise false.

How can we use LIKE ANY() Function ?

LIKE ANY() function is used to allow matching of strings based on comparison with the given patterns. String matching is case-sensitive.

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 Any() Function in Snowflake?

-- Syntax : 

Input_column LIKE Pattern [ ESCAPE ]

LIKE ANY() Argument Details :

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

Input_Column :

An Input_Column that compares the patterns. Typically varchar, although some other datatypes can be used.

Pattern :

Patterns that the string is to be compared to. Typically 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 indicate that the wildcard should be interpreted as a regular character and not as a wildcard.

Return :

The data type of returned value is VARCHAR.

Examples of LIKE ANY() function in Snowflake:

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

Create and Insert the data into 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 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 ANY() function 

select * from demo.public.emp 
where name like any ('%Ra%','S%')
order by name;

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

As we can see from 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 that satisfies both the patterns.

Examples for making use of LIKE ANY() function with Escape

select * from demo.public.emp
where name like any ('%Ra%','S^a%') escape '^'
order by name;

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

Full Example of LIKE ANY() 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 Any to table data

select * from emp
where name like any ('%Ra%','S%')
order by name;

+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  7 | Rajiv Ram   | NOT-NULL |
|  1 | Ram         | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  3 | Sameer      | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
+----+-------------+----------+

When you should use LIKE Any Function in Snowflake?

There are certain use case scenarios when it is recommended to use the Like 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 LIKE 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’.

LIKE ANY Snowflake Official Documentation Link

Final Thoughts

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

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar