Impossible Joins … unless you use STRING_SPLIT() | Essential SQL 

Learn how to do an impossible join using STRING_SPLIT.
Learn to join on dirty data, by tokenizing a column. That is splitting it into words, which you can then join to other tables.
This is useful if those pesky humans have entered extraneous data around project codes, or other data you wish to relate. For example, in one table you have “ABC12” and in another “PRE ABC12 (RED)”
Seems like an impossible join, until you look at the values and see you can split up the once column into three separate words!
One Last Thing...
This video is part of my Beginner Learning series. If you're interested in learning SQL subscribe to @Essential SQL and then check out our Intermediate Learner Playlist.
Of course, I also encourage you to visit www.essentials... to learn even more!
Source Code:
-- Project
create table Project
(
id int,
ProjectName varchar(40),
AccountNumber varchar(20)
)
insert into Project
select '1','Bike M385 (new)', 'A0124' union all
select '2','Bike R797 (new)', 'A0123' union all
select '3','9108 ', 'A0123' union all
select '4','F1432 plastic', 'A0124' union all
select '5','FK89Z WH CHICAGO', 'A1239'
--select * from Project
--Project Status
create table ProjectStatus
(
ProjectName varchar(40),
ProjectStatus varchar(10)
)
insert into ProjectStatus
select 'M385', 'Active' union all
select '9108', 'New' union all
select 'F1432', 'Active' union all
select 'FK89Z', 'New' union all
select 'R797', 'Closed'
-- select * from ProjectStatus
--what happens if you just try to join on ProjectNames as is?
Select s.ProjectName, s.ProjectStatus, p.AccountNumber
from ProjectStatus s
left join Project p on s.ProjectName = p.ProjectName
--Let's use String Split to Tokenise the ProjectName... we can then try to join on the pieces.
--Here is how string split works
select value [StringSplit Result]
from Project
cross apply string_split(ProjectName,' ')
--Here is the tokenized table with account numbers.
select value ProjectName, AccountNumber
from Project
cross apply string_split(ProjectName, ' ')
--now let's take our original query and replace the join to Project with
--the tokenized join.
Select s.ProjectName, s.ProjectStatus, p.AccountNumber
from ProjectStatus s
left join (
select value ProjectName, AccountNumber
from Project cross apply string_split(ProjectName, ' ')
) p on s.ProjectName = p.ProjectName
-- drop table project
-- drop table projectstatus