Left Joining after Case Statment SQL-CodePudding

I have two tables, A and B In the table A there's one column with a Full Name called EmployeeName, on the table B there's also one column with the name OrigFullName, the thing is the column EmployeeName don't follow a standard, sometimes there's "FirstName LastName" and sometimes "LastName, FirstName".

I could fit the table A in the same standard as table B using the following code


CASE 
  when charindex(',', EmployeeName) = 0 then EmployeeName
  ELSE CONCAT(RIGHT(EmployeeName, LENGTH(EmployeeName) - CHARINDEX(',', EmployeeName)), ' ', LEFT(REPLACE(EmployeeName, ',', REVERSE(EmployeeName)), charindex(' ', EmployeeName)-2))
END AS name

FROM TableA

After it, the column that I create match with the format of the column OrigFullName.

My next step would be to check which names are in the Table A and in the Table B and count it.

I've tried to left join both tables with the following code:

SELECT 

CASE 
  WHEN charindex(',', EmployeeName) = 0 THEN EmployeeName
  ELSE CONCAT(RIGHT(EmployeeName, LENGTH(EmployeeName) - CHARINDEX(',', EmployeeName)), ' ', LEFT(REPLACE(EmployeeName, ',', REVERSE(EmployeeName)), charindex(' ', EmployeeName)-2))
END AS a.name,
b.OrigFullName

FROM TableA AS a 
LEFT JOIN TableB AS b ON a.name = b.OrigFullName

GROUP BY 1,2

But unfortunately it didn't work I feel like I'm very close but since I'm a beginner I really cannot find where the mistake are. In the end of it, my main goal is to compare how many names in TableA matches with the names in the TableB after the names in the TableA follows the standard "FirstName LastName" like the TableB

CodePudding user response：

You missed a -1 in your right function, check this out:

WITH [A] AS (
        SELECT *
        FROM (VALUES ('Dwayne Johnson')
            ,('Biden, Joe')
            ,('Musk, Elon')) AS [T]([EmployeeName]))
    ,[B] AS (SELECT *
        FROM (VALUES ('Dwayne Johnson')
            ,('Joe Biden')
            ,('Elon Musk')) AS [T]([OrigFullName]))
    ,[A_Clean] AS (
        SELECT [EmployeeName]
            ,IIF(CHARINDEX(',', [EmployeeName]) = 0, [EmployeeName], CONCAT(RIGHT([EmployeeName], LEN([EmployeeName]) - CHARINDEX(',', [EmployeeName]) - 1), ' ', LEFT(REPLACE([EmployeeName], ',', REVERSE([EmployeeName])), CHARINDEX(' ', [EmployeeName]) - 2))) AS [EmployeeName_Clean]
        FROM [A])
SELECT [A_Clean].[EmployeeName]
    ,[B].[OrigFullName]
FROM [A_Clean]
    LEFT JOIN [B]
        ON [B].[OrigFullName] = [A_Clean].[EmployeeName_Clean]

CodePudding user response：

I could solve it by writting the query this way

SELECT DISTINCT

CASE 
  WHEN charindex(',', EmployeeName) = 0 THEN EmployeeName
  ELSE CONCAT(RIGHT(EmployeeName, LENGTH(EmployeeName) - CHARINDEX(',', EmployeeName)), ' ', LEFT(REPLACE(EmployeeName, ',', REVERSE(EmployeeName)), charindex(' ', EmployeeName)-2))
END) AS Name,
a.EmployeeName
b.OrigFullName

FROM TableA AS a 
LEFT JOIN TableB AS b ON TRIM(LOWER(b.OrigFullName)) = TRIM(LOWER(CASE 
  WHEN charindex(',', EmployeeName) = 0 THEN EmployeeName
  ELSE CONCAT(RIGHT(EmployeeName, LENGTH(EmployeeName) - CHARINDEX(',', EmployeeName)), ' ', LEFT(REPLACE(EmployeeName, ',', REVERSE(EmployeeName)), charindex(' ', EmployeeName)-2))))
END)

GROUP BY 1,2,3 
HAVING a.OrigFullName IS NOT NULL

This way I've got a table with the EmployeeName in the standard and the matches from EmployeeName and OrigFullName