CODEWITHSUNDEEP
sqljoinmatching
Giedrius Urbonas
Giedrius Urbonas

Reputation: 83

Matching pattern in SQL

I have two tables and need to match all records by name. The problem is that in one table name format is FirstName LastName, in another table - LastName FirstName, and I cannot split into separate columns because some records might have few first names or last names, so I don't know where first or last name ends or starts.

Eg. in first table I have John Erick Smith and need to join all records from another table where the name is Smith John Erick.

Any solution in SQL?

Upvotes: 1

Views: 124

Answers (2)

RandyMcKay
RandyMcKay

Reputation: 326

I think you can use string functions to get the piece of string (in 'John Erick Smith' type column) after the last space as a surname and stick it to front. Then you could compare the strings. That is assuming you don't have spaces in surnames.

Here is MSDN article on how to do it.

DECLARE @string nvarchar(max) = 'FirstName SecondName Surname'

SELECT RIGHT(@string, NULLIF(charindex(' ', REVERSE(@string)),0)) + ' ' + 

REVERSE(RIGHT(REVERSE (@string), len(@string) - NULLIF(charindex(' ', REVERSE(@string)),0)))

Returns:

Surname FirstName SecondName

Upvotes: 1

yellowvamp04
yellowvamp04

Reputation: 141

Verify first if you still have other tables with "FirstName" and "LastName" that you can use instead of using the field with "FirstName LastName". Normally Oracle has this kind of tables use for persons/employees. You may have something like this.

But if the "LastName FirstName" uses "," (comma) in its data then you can do a substring to get the LastName from the FirstName.

Or another alternative is by using their IDs (eg. employee IDs) [if only available].

Upvotes: 0

Related Questions