New SQL Capabilities in Snowflake ❄️

Snowflake has introduced innovative SQL techniques that will significantly simplify tasks for coders. Over the past three decades, SQL has established itself as the standard language for data interaction. Hence, we must embrace these new and improved versions of SQL to streamline our daily operations effectively.

In this article, we will go through these new syntaxes one by one with examples and use cases.

Let’s create a STUDENT table in the snowflake as below before looking into these new syntaxes.

CREATE DATABASE IF NOT EXISTS DB_TEST;
CREATE SCHEMA IF NOT EXISTS DB_TEST.TEST;
CREATE TABLE IF NOT EXISTS DB_TEST.TEST.STUDENT
(
ID NUMBER,
STUDENT_NAME VARCHAR,
SUBJECT VARCHAR,
MARKS NUMBER
);
INSERT INTO DB_TEST.TEST.STUDENT
VALUES
(1,'as','math',90),
(2,'kp','math',23),
(3,'sp','math',76),
(4,'dp','science',89),
(1,'as','science',43),
(2,'kp','science',12),
(3,'sp','science',45),
(4,'dp','math',98);

SELECT * FROM DB_TEST.TEST.STUDENT;

The output of the test table

LIKE ALL
Enables case-sensitive string matching by comparing it with one or multiple patterns. In the general LIKE operation, you can compare strings with only one pattern but with LIKE All you can compare more than one pattern at a time. If and only if the input string matches all of the patterns, this returns the input string.

SELECT STUDENT_NAME
FROM DB_TEST.TEST.STUDENT
WHERE
STUDENT_NAME LIKE ALL ('%p','_p')
GROUP BY STUDENT_NAME;

-----OUTPUT----
STUDENT_NAME
kp
sp
dp

In the above example, we are comparing STUDENT_NAME with strings ending with ‘p’ and two-letter strings ending with ‘p’.

LIKE ANY
Similar to the LIKE ALL but returns the input string if the input string matches any of the patterns.

SELECT STUDENT_NAME
FROM DB_TEST.TEST.STUDENT
WHERE
STUDENT_NAME LIKE ANY ('_p','a_')
GROUP BY STUDENT_NAME;

-----OUTPUT----
STUDENT_NAME
as
kp
sp
dp

In the above example, we are comparing STUDENT_NAME with two-letter strings ending with ‘p’ and two-letter strings starting with ‘a’.