Snowflake- Pattern Matching with Inner join

Question

In snowflake Can we join on a column between 2 tables based on regex/substring instead of exact equality?

For example In Table A/Column A and Table B/Column B, fetch all the records where Column A is a substring of Column B.

I referred REGEXP_SUBSTR, SUBSTR, SUBSTRING and CONTAINS functions of the snowflake, but couldn't figure out how to use it as part of Inner JOIN.

Rich Murnane Rich Murnane · Accepted Answer · 2020-05-21T11:30:43

You can do it with substrings, charindex functions, and probably some regexp commands too, here are a couple overly simple examples.

CREATE TABLE table_a (column_a VARCHAR(100));
INSERT INTO table_a VALUES ('hello world'),('testing 123'),
            ('I like Jelly'),('this is a good question');

CREATE TABLE table_b (column_b VARCHAR(100));
    INSERT INTO table_b VALUES ('this is table b'),('world'),
       ('jelly'),('Netflix or Hulu?'),('Goodbye');

SELECT a.*
FROM   table_a a
INNER JOIN table_b b 
   ON SUBSTR(a.column_a, CHARINDEX(' ', a.column_a) + 1, length(a.column_a))
      = b.column_b;
--1 row selected, 'hello world'


SELECT a.*, b.*
FROM   table_a a,
       table_b b 
WHERE  CHARINDEX(UPPER(b.column_b), UPPER(a.column_a)) > 0;
--2 rows selected, 'hello world'/world & 'I like Jelly'/jelly

Snowflake- Pattern Matching with Inner join

1 Answers