CODEWITHSUNDEEP
c#asp.net
Paks
Paks

Reputation: 1470

Seperate Values which are saved in one column

i have one column. In that column is saved the fullname(firstname, surname) of a Person. Sometimes it is saved like

Michael, Myers

and sometimes the name is saved like

Michael Myers

without a comma between them.

If i load the column and save it to a variable it looks of course like: Michael, Myers or Michael Myers.

The Question is: If i load the column from the database, how can i save the the firstname and the surname independently of each other in different variables.

Upvotes: 0

Views: 110

Answers (4)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

As others have said splitting on a comma is trivial. The real problem is you don't know how many more flavours are going to turn up. If it's only these two then

Following assumes sql server is your db, something like 

Select 
Case 
 When CharIndex(',',[DaftNameField]) = 0 
 Then Substring([SomeNameField],CharIndex(',',[SomeNameField]),255)
else  Substring([SomeNameField],1, CharIndex(' ',[SomeNameField]) - 1)
end As Forename,
Case 
 When CharIndex(',',[SomeNameField]) = 0 
 Then Substring([SomeNameField],1 CharIndex(',',[SomeNameField]) - 1)
else  Substring([SomeNameField], CharIndex(' ',[SomeNameField]) + 1, 255)
end As Surname
From SomeTable

Off the top of my head this, but it's basically right.

As you can see already painful, add in middle names, honorifics and such, gets worse.

Oh and please tell me there's some sort of PersonID in this table.

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

This is a data nightmare. I would strongly suggest correcting this at the source (use two separate columns) and fixing the data once if that is at all possible.

Assuming it is not (or not right now):

You will get most cases right by just trying to look for a comma and using branch logic based on the result. However, there are many edge cases to consider. The outline of one approach would be

string[] parts = theColumn.Trim().Split(',');
if (parts.Length == 1)
{
    // Find the last occurrence of ' ' and split first/last name based on that
    // People may have middle names entered e.g. Michael M. Myers
}
else if (parts.Length == 2)
{
    firstName = parts[1];
    lastName = parts[0];
}
else
{
    // Dealing with a more complex case like Myers, Jr., Michael
    // You will have to develop logic for such special cases that may
    // be in your data.
}

You will run into problems with the single-comma case with names like Michael Myers, Jr. or Michael Meyers, DDS. More complete logic would test for such cases.

You may run into cases of 2 (or more) commas in the Last, First format when there is some sort of name suffix. You will have to decide how much time to spend on sanitizing logic there. Personally I would tend to log all cases where that happened for a few months to evolve my logic.

I used a product several years back that did a good job pulling names out of free-form fields and sorting out what was the first name, last name, etc. Might be worth having a look at.

Upvotes: 2

Dave Zych
Dave Zych

Reputation: 21887

What are the possible values of fullname? Given your example above, there's no good, consistent way to separate the two, other than something lame like:

string firstName;
string lastName;
if(fullname.Contains(","))
{
    string[] splitNames = fullName.Split(",");
    lastName = splitNames[0];
    firstName = splitNames[1];
}
else if(splitNames.Contains(" "))
{
    string[] splitNames = fullName.Split(" ");
    firstName = splitNames[0];
    lastName = splitNames[1];
}
else
{
    //Some other logic.
}

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

You can do a string split on ", ". Then another string split on " ". This will make sure you've covered both cases.

BUT, what you really want to do is hit your DBA with a large wooden object for stuffing two columns into one.

Upvotes: 0

Related Questions