SQL Day 20: Advanced SQL Queries for Practice
You can download all the PPTs and Queries to Practice from below Udemy Course.
My Udemy Course on SQL in Telugu:
https://www.udemy.com/course/sql-dpif...
Use this coupon for Discount:
553550D47CD57BBD4864
===============
Regular expressions Queries: (Some queries are there in the comments section)
-- Detailed explanation about Regular Expressions is given in below page.
-- https://docs.snowflake.com/en/sql-ref...
-- Set up sample data
CREATE TABLE PUBLIC.REGEXP(text varchar(100));
INSERT INTO PUBLIC.REGEXP(text) VALUES
('Snowflake'),
('Snow flake'),
('Snow-flake'),
('Snow123flake'),
('Snowflake Regexp'),
('Regular Expression'),
('Snowflake contains 3 layers'),
('Regexp with \\backslash'),
('12345');
SELECT * FROM PUBLIC.REGEXP;
/* ----------
1. REGEXP - Returns true if the subject matches the specified pattern.
Both inputs must be text expressions
---------- */
-- To search all the strings starts with word Snow
SELECT text from PUBLIC.REGEXP where text REGEXP 'Snow.*';
-- To search all the strings starts with word Regexp
SELECT text from PUBLIC.REGEXP where text REGEXP 'Regexp.*';
-- To search all the strings that contains word Regexp
SELECT text from PUBLIC.REGEXP where text REGEXP '.*Regexp.*';
-- To search all the strings that contains Digits(0-9)
SELECT text from PUBLIC.REGEXP where text REGEXP '.*\\d.*';
or
SELECT text from PUBLIC.REGEXP where text REGEXP '.*[0-9].*';
-- To search all the strings that starts with Digits(0-9)
SELECT text from PUBLIC.REGEXP where text REGEXP '\\d.*';
or
SELECT text from PUBLIC.REGEXP where text REGEXP '.*^[0-9].*';
-- To search all the strings that contains Spaces
SELECT text from PUBLIC.REGEXP where text REGEXP '.*\\s.*';
-- To search all the strings that matches whole words
SELECT text from PUBLIC.REGEXP where text REGEXP '\\bSnow\\b.*';
-- To search all the strings that contains space followed by backslash
SELECT text from PUBLIC.REGEXP where text REGEXP '.*\\s\\\\.*';
/* ----------
2. REGEXP_COUNT - Returns the number of times that a pattern occurs in a string.
---------- */
-- Number of occurences of 'is'
SELECT REGEXP_COUNT('Snowflake is a cloud database, this IS best in the market', 'is', 1) as result
from DUAL; -- 3
-- Number of occurences of whole word 'is'
SELECT REGEXP_COUNT('Snowflake is a cloud database, this is best in the market', '\\bis\\b', 1) as result
from DUAL; -- 2
-- Number of occurences of 'is' -- with case-sensitive
SELECT REGEXP_COUNT('Snowflake is a cloud database, this IS best in the market', 'is', 1, 'c') as result
from DUAL; -- 2
-- Number of occurences of whole word 'is' -- with case-sensitive
SELECT REGEXP_COUNT('Snowflake is a cloud database, this IS best in the market', '\\bis\\b', 1, 'c') as result
from DUAL; -- 1
-- Number of occurences of whole word 'is' -- with case-insensitive
SELECT REGEXP_COUNT('Snowflake is a cloud database, this IS best in the market', '\\bis\\b', 1, 'i') as result
from DUAL; -- 2
/* ----------
3. REGEXP_SUBSTR_ALL - Returns an ARRAY that contains all substrings that match a regular expression within a string. If no match is found, the function returns an empty ARRAY.
---------- */
-- To get all occurances of letter 'a' followed by a digit
SELECT regexp_substr_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]') FROM DUAL;
SELECT regexp_extract_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]') FROM DUAL;
-- To get all occurances of letter 'a' followed by a digit from 4th position
SELECT regexp_substr_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]', 4) FROM DUAL;
SELECT regexp_extract_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]', 4) FROM DUAL;
-- To get all occurances of letter 'a' followed by a digit starting from 3rd occurance
SELECT regexp_substr_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]', 1, 3) FROM DUAL;
SELECT regexp_extract_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]', 1, 3) FROM DUAL;
-- To ignore case sensitive matches
SELECT regexp_substr_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]',1,4,'i') FROM DUAL;
SELECT regexp_extract_all('a1_a2a3_a4A5_a6a6', 'a[[:digit:]]',1,4,'i') FROM DUAL;
-- To get all the digits into an array from the string
SELECT regexp_substr_all(text, '[[:digit:]]') FROM PUBLIC.REGEXP;
SELECT regexp_extract_all(text, '[[:digit:]]') FROM PUBLIC.REGEXP;