Skip to content
Advertisement

Remove characters after space in sql

I have two tables as shown below:

Table1 Table2

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))
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement