Remove characters after space in sql

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)

​x
 
SELECT * FROMtable1 A  JOINtable2 B ON LEFT(upper(A.FIRST_NAME)||' ', CHARINDEX(' ', upper(A.FIRST_NAME)) - 1)= LEFT(upper(B.FIRSTNAME)||' ', CHARINDEX(' ', upper(B.FIRSTNAME)) - 1)​

Please suggest!

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

 
select * from A join B onupper(trim(split(A.FIRST_NAME, ' ')[0]::string)) = upper(trim(split(B.FIRST_NAME, ' ')[0]::string))​

Advertisement

Answer