I have two tables as shown below:
I would like to match these two columns i.e FIRST_NAME from table1 to FIRSTNAME from table2.
I tried with the below query but only could match Richard and not Kristin.
SELECT * FROM table1 A JOIN table2 B ON LEFT(upper(A.FIRST_NAME)||' ', CHARINDEX(' ', upper(A.FIRST_NAME)) - 1)= LEFT(upper(B.FIRSTNAME)||' ', CHARINDEX(' ', upper(B.FIRSTNAME)) - 1)
Please suggest!
Advertisement
Answer
I would add trim to make sure there’s no whitespace preventing a match.
select * from A join B on upper(trim(split(A.FIRST_NAME, ' ')[0]::string)) = upper(trim(split(B.FIRST_NAME, ' ')[0]::string))