March 5, 2007 at 1:44 pm
I have two tables. One table stores employees in a full name column, the other table stores employees in three different columns a last name, first name, and middle intial column. For instance
Table 1 - full name:
Doe, John
Smith, Jack W
Cole, Jill M.
Weight Sr., Jim
The middle initial may or may not have a period after it.
Table 2 - last name, first name, middle initial (| designates column break):
Doe | John |
Smith | Jack | W
Cole | Jill | M.
Weight Sr. | Jim |
How can I join these two tables so that we recoginze that table1.Doe, John = table2.Doe | John? Is this possible?
March 5, 2007 at 2:46 pm
Derived table to contruct FullName from the 3 name parts, then join on the concatenated column of the derived table
Select *
From Table1
Inner Join
(
Select *,
Rtrim(LastName + ', ' + FirstName + ' ' + IsNull(MiddleInitial, '')) As FullName
From Table2
) dt
On (dt.FullName = Table1.FullName)
March 6, 2007 at 4:45 am
Hi ,
Try This.. Its working..
select * from full_name1 a ,
full_name2 b
where soundex(a.col_1)=soundex(b.col_1)
Regards ,
Amit G.
March 6, 2007 at 8:53 am
No. It's not working:
Select Soundex('Smith, John')
Select Soundex('Smyth, Jon')
March 6, 2007 at 9:47 am
Hi,
But as per given requirement it is working fine...
Regards
Amit
March 6, 2007 at 10:15 am
The only given requirement is this:
How can I join these two tables so that we recoginze that table1.Doe, John = table2.Doe | John?
I don't see any requirement for fuzzy matching. I see a requirement that:
"Doe, John" is exactly equal to "Doe, John"
If you see a requirement that "Doe, John" is supposed to join to
"Doe, John"
"Doe, Jon"
"Do, John"
"Doh!, John"
... using a fuzzy matching Soundex, then I feel for whomever has to write your requirements, because they might as well write the SQL code in order to avoid any ambiguity or wiggle room.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply