How to split firstname and lastname from a name which has 4 words.? in C# and query using it in Mysql

Question

I get user name from a list box, I split the first name and lastname and with that I use that to find their userid from DB,

 string FirstName = li.Value.Split(' ')[0].Trim();
   string lastName = li.Value.Split(' ')[1].Trim();

But the tricky part here for me is when the user name is Mark Van Harm Jiti the firstname is Mark Van and Surname is Harm Jiti. But my code takes Mark as first name and Van as surname. But I wont know which is first name and which is surname. I just get the text Mark Van Harm Jiti from the list box,. How should I change my query so that I get the User id for the user Mark Van Harm Jiti

string getuserid = "SELECT UserID from tbl_User Where FirstName=@FirstName AND LastName=@LastName";

I am using Mysql DB and it is c# code.

What about names with three or five words, or more? What about 4-word names in which the surname is three words ("Georges de la Tour")? — phoog, Nov 15 '11 at 18:28
Possible duplicate of http://stackoverflow.com/q/5596706/385844 — phoog, Nov 15 '11 at 18:33

jason · Answer 1 · 2011-11-15T18:48:49.103

If you can safely assume that the last name is the last token, you can do the following.

string[] tokens = li.Value.Split(' ');
string firstName = tokens[0].Trim();
string lastName = tokens[tokens.Length - 1].Trim();

However, that's a bad assumption (cf. Luiz Inácio Lula da Silva, his last name is "da Silva" or Cristiano Ronaldo dos Santos Aveiro whose surname is "dos Santos Aveiro"). It's far better to not be in the position where you have to discern the first and last names from a single string.

But the tricky part here for me is when the user name is Mark Van Harm Jiti the firstname is Mark Van and Surname is Harm Jiti. But my code takes Mark as first name and Van as surname.

Yeah, see, examples like this basically confirm that you have the wrong approach in trying to determine the first and last names from a single string. There is no logic that will sometimes pick out the last two tokens and sometimes pick out only the last token (my brother has two middle names, so you can't use the logic of always taking the last two tokens because on "Mark Van Harm Jiti" it's right, but on my brother's name it would be wrong).

select userid from tbl_user where Concat(Firstname,' ', lastname)='Mark Van Harm Jiti'; DO you think this is a good solution for the problem — John, Nov 15 '11 at 18:37
"There is no logic that will sometimes pick out the last two tokens and sometimes pick out only the last token " - BUT there is logic to try all the possiblities. See my answer please. — Shai Cohen, Nov 15 '11 at 19:59

score 0 · Answer 2 · answered Nov 15 '11 at 18:23

I think you need to do a word count to check the number of words in a name then branch your logic depending on the count. My word count function is below. I use this to check for the number of tags in a textfield and then depepnding on the number either split and insert or if its just 1 tag insert. I think something similiar is what you need. Based on the count split between Van and Harm.

     Public Shared Function WordCount(ByVal s As String) As Int32
        Dim collection As MatchCollection = Regex.Matches(s, "[\S]+")
        Return collection.Count
    End Function

select userid from tbl_user where Concat(Firstname,' ', lastname)='Mark Van Harm Jiti'; DO you think this is a good solution for the problem — John, Nov 15 '11 at 18:37

score 0 · Answer 3 · answered Nov 15 '11 at 18:29

0

Maybe you are approching this incorrectly. How is the ListBox populated? Can the UserID be stored for each ListItem value when the ListBox is populated? If you did it that way you could just access the SelectedValue of the ListBox to get the UserID.

answered Nov 15 '11 at 18:29

kevev22

3,737
21
32

score 0 · Answer 4 · answered Nov 15 '11 at 18:30

0

I believe you could adjust your query to this:

string getuserid = "SELECT UserID from tbl_User Where CONCAT(FirstName,' ', LastName)=@userName";

and change your code to:

string userName = li.Value.Trim();

answered Nov 15 '11 at 18:30

pstrjds

16,840
6
52
61

score 0 · Answer 5 · answered Nov 15 '11 at 18:55

The only way I can think to cover all possibilities is to check for all permutations. The SQL statement should eventually end up like this:

(FirstName = 'Mark Van Harm' AND LastName = 'Jiti) OR (FirstName = 'Mark Van' AND LastName = 'Harm Jiti) OR (FirstName = 'Mark' AND LastName = 'Van Harm Jiti)

Let me know if this is an approach you are interested in and I'll be more than happy to provide some sample code

How to split firstname and lastname from a name which has 4 words.? in C# and query using it in Mysql

5 Answers5