Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: Compare two columns?

    Dear All,

    I want to join two tables based on the NAME field in each table. However, the format of the NAME field in each table don'f follow the exact same patterns. How can I achieve this in MS SQL Server?

    Appreciate your help in advance,

    Russel

    Examples are:

    Table A: Smith, Joe B. JR.
    Table B: Smith, Joe JR

    Table A: Taylor, Robert M. III
    Table B: Taylor, R. M. III

    Table A: Macelod, Robert B SR
    Table B: Macelod, Bob B. SR.

  2. #2
    Join Date
    Jan 2010
    Posts
    18
    You can't.

    Matching by names and addresses is usually a nightmare if that's all you've got to match on. Especially as most systems let you put anything into the name columns. So you get Robert, Rob, Bob, Rab, R and R. supposedly meaning the same. And somebody called John in one table could be called Jack in another.

    You need to get some kind of ID into each of the tables so that you can match on ID.

    Even if the names were in the same format, you can't tell if they are the same person just by looking at the name. If you have more than one John Smith, how do you know which one is which if all you've got is the name?

    No easy answer.

  3. #3
    Join Date
    Jan 2010
    Posts
    18
    This is a similar thread that gives you more options.

    http://www.dbforums.com/microsoft-sq...o-strings.html

    good luck

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try creating this function, which may help you along your way;
    http://dl.dropbox.com/u/2507186/Func...FormatName.sql

    Your syntax would be:
    Code:
    select	*
    from	TableA
    	inner join TableB
    		on dbo.FormatName(A.Name, 'F') = dbo.FormatName(B.Name, 'F')
    		and dbo.FormatName(A.Name, 'm') = dbo.FormatName(B.Name, 'm')
    		and dbo.FormatName(A.Name, 'L') = dbo.FormatName(B.Name, 'L')
    ...which would compare the name elements individually, or this:

    Code:
    select	*
    from	TableA
    	inner join TableB
    		on dbo.FormatName(A.Name, 'FmLs') = dbo.FormatName(B.Name, 'FmLs')
    ...which would format the names identically before comparing them.

    But you should also consider using this function to split the names permanently and store the parts in different columns in your table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •