How to split a string column and then take a join? if any of the split value match, join is successful - Snowflake

Question

In Snowflake I have 2 tables, Candidate and Employee. I want to split column CandidateName by spaces and then take a join with Employee on column EmployeeName. For example, if I split Ali - Hasan Khan then it will give me 4 values: Ali, -, Hasan, Khan. If any of these 4 values are in column EmployeeName then the join will be successful.

Candidate:

CandidateId	CandidateName
1	Muhammad Bilal
2	Ali - Hasan Khan
3	Tehseen Nawaz

Employee:

EmployeeName	StartDate
Muhammad Bilal	2022-02-15
Ali Hasan	2022-03-17
Tehseen Nawaz Virk	2022-01-10

Desired result:

CandidateName	StartDate
Muhammad Bilal	2022-02-15
Ali - Hasan Khan	2022-03-17
Tehseen Nawaz	2022-01-10

Answer 1

You can use ARRAY_INTERSECTION for this:

create table candidate (CandidateId number,     CandidateName varchar )
as select * from values 
(1, 'Muhammad Bilal'),
(2, 'Ali - Hasan Khan'),
(3, 'Tehseen Nawaz');

create table employee 
( EmployeeName  varchar, StartDate date) as select * from values
('Muhammad Bilal',  '2022-02-15'),
('Ali Hasan','2022-03-17'),
('Tehseen Nawaz Virk',  '2022-01-10');

select c.CandidateName, e.StartDate
from candidate c
join employee e on ARRAY_INTERSECTION( split(e.employeename,' '), split(c.candidatename,' ')) <> [];

+------------------+------------+
|  CANDIDATENAME   | STARTDATE  |
+------------------+------------+
| Muhammad Bilal   | 2022-02-15 |
| Ali - Hasan Khan | 2022-03-17 |
| Tehseen Nawaz    | 2022-01-10 |
+------------------+------------+