(C#- WIndows Forms) Separating FullName field into LastName, FirstName and MiddleName in SQL via C# RRS feed

  • Question

  • Hello, I'm currently using Visual Studio C# and am utilizing Windows Forms, and was wondering how I'd be able to merge two table together in sql using c#.

    Table1= FullName, Gender, Address
    Table2= LastName, FirstName, MiddleName, Gender, Address

    If Table2 had FullName instead of it's names being separated into 3 (LastName, FirstName, MiddleName), it would be a piece of cake. But my goal is to separate Table1's FullName into LastName, FirstName, and MiddleName first before merging both tables together.

    An example of a FullName that I want to separate would be "Holmes, Mary Beth" (Holmes being the LastName, Mary being the FirstName, and Beth being the MiddleName)

    How do I go about this? Any help would be greatly appreciated, thank you. ^_^

    Thursday, March 22, 2018 12:50 PM

All replies

  • Is there a comma separating the last, middle and first name? If so, then you can use the Split function to split up the FullName into the different parts:

    The Replace method can be used to remove the comma and any other unnecessary characters.

    The Trim method can be use to remove leading and trailing spaces.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 22, 2018 1:46 PM
  • My company has to deal with this problem all the time and I'm afraid to say there is no algorithm that is going to work correctly in all cases. Heuristics will get you only so far. It is generally easier to combine names than to separate them. You'll need to determine the algorithm that works bests for you but here's the scenarios you'll need to account for.

    normal - F M L
    no middle - F L
    Multi word first - F1 F2 M L (i.e. Mary Ann)
    Multi word last - F M L1 L2 (i.e. St John)
    Combinations of multi word first and last (or middle)
    Singular names - F (common in foreign countries)

    It is easier to combine first, middle and last for comparison purposes. You might consider going that route instead.

    Michael Taylor

    Thursday, March 22, 2018 2:15 PM
  • Start by writing a program that attempts to do the match and see how many are not matched. So I guess you would create a temporary table of the data from Table1 in the format that should match Table2 then do a join and the number of mismatches would be interesting. Then attempt to determine the cause of the mismatches. Then you can refine the reformat algorithm and try again. Keep doing it until you get to the point that it would be more economical to fix the remaining mismatches manually.

    I once wrote a program that attempted to pick out the tools and materials in some manufacturing instructions for aircraft. I hope that converting names will be easier.

    Perhaps it will help to begin with just last names. Create queries for unique last names and match them and look at the mismatches. Correct any mistakes that can be found. Then do that for first names. If you do as much as can be done that way then there will be less to do when the entire names are matched.

    Sam Hobbs

    Thursday, March 22, 2018 8:28 PM