I've been tasked with what so far seems not possible but I was hoping that someone better at sql queries then me could figure this out if it's possible. I'm having problems querying data from a table. The source of the problem is I'm forced to query on names an date of birth. The date of birth is working and is out of the scope of my issue. My goal is to query using common values in both the source (an excel report) and the destination (the database) and thats the last name.
Fields: Name1, Name2 Table: Participant
Name2 in the database contains last name but if the person uses a middle name or a suffix it also contains those values. The source (report) for the most part contains only last names but sometimes it also has a small number of middle names also mixed into the last name. My goal is to strip out the middle name and suffix from the database query and also from the report last name string.
From the Database: I need to strip the middle name which is to the left of the last name in the name2 field. They are separated by a space. I will also need to strip the suffix if it exists after the last name.
From the Report: I need to strip out the middle name which would be to the left of the last name separated by a space. This would be done in c#
Please let me know if I can provide any more info to help with an answer.
My first guess with the query part is to do a wildcard search in which I would obtain the last name from the report and query the table using a (like '%%'). I think this will obtain the record I'm looking for in a search but I'm not sure how well it will work.
As John Clifford suggests, you could grab the last name using split like so (pseudocode):
// If the string contains a space, split it
string surname = Name2;
int spacePos = surname.IndexOf(" ");
if (spacePos > 0)
{
string[] words = surname.Split(' ');
surname = words[1];
}
Or you could find the space, and then get a substring:
int spaceTruckin = Name2.IndexOf(" ");
string surname = Name2.Substring(spaceTruckin);