March 15, 2012 at 1:20 pm
I have situation where I have nest full name contain format like
First Name Initial Last Name
First Name Last Name
Last Name, First Name Initial
Last Name; First Name
Last Name ,First Name
How I can white query for all those scenarios?
Thank you in advance
March 15, 2012 at 1:26 pm
unclear on what you trying to do.. Can you be more elaborate?
And what is your desired output of ur samples?
March 15, 2012 at 1:39 pm
It should be display in Last_Name, First_Name, Initial columns
March 15, 2012 at 1:42 pm
Last_NameFirst_NameInitial
Smith Antony I
March 15, 2012 at 1:53 pm
I have 5 different layout in one column called Full_Name
I need split to Last- First and Init Name column
How I can do this?
March 15, 2012 at 1:59 pm
well in theory, you split the column on the space betwen the words...
but the issue is, "you cannot tell the which is the "middle" name between "James Tiberius Kirk" and Public, John Q."
Lowell
March 15, 2012 at 2:30 pm
Lowell (3/15/2012)
well in theory, you split the column on the space betwen the words...but the issue is, "you cannot tell the which is the "middle" name between "James Tiberius Kirk" and Public, John Q."
Plus, if both names above are in the table, how do you decide which is the first name and which is the last?
We know that James is Kirk's first name and Public is John's last name, but how do you code that in to a procedure. Plus, what if a persons name if Frank Paul. Yes, I knew some one with this name. Is Frank their first name of last name?
March 15, 2012 at 3:32 pm
And then there is Mary Jo Van Essen.
First Name Initial Last Name
First Name Last Name
Last Name, First Name Initial
Last Name; First Name
Last Name ,First Name
Is the initial always one character?
Does the semi-colon only appear in the Last Name/First Name scenario?
March 15, 2012 at 3:52 pm
You could split by "up to punctuation" for LastName, First type formats, splitting anything before the punctuation (either comma or semicolon) as the last name. That would be Left(FullName, patindex('%[,;]%', LastName) as the calculation for that.
First and Middle are trickier. That works if there's a single-letter Middle Initial, but is otherwise problematic. Will assuming a single-letter substring is the middle initial work for your data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply