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:

Employee:

Desired result:

CodePudding user response：

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 |
 ------------------ ------------