Name Field seperation
-
I am trying to take a name field and divide it into three fields called Firstname, MI, LastName. The data is in a format like Smith, John L most of the time, but sometimes the full middle name is listed like Smith, John Lee or there is no MI to break out. I only need it to break out into John L Smith. If worst comes to worst I would take a clean First and Last name. I have been tweaking the following query and I am close to getting what I need. If anyone can help with the final couple tweaks I would greatly appreciate it. thanks
SELECT SUBSTRING(NAME,1,CHARINDEX(',',NAME)-1) as LastName,
SUBSTRING(NAME,CHARINDEX(',',NAME)+1,LEN(NAME)) as FirstName,
RIGHT(NAME, 1) AS MI
from maystaff
-
I hate doing string manipulation like this, but here you go. REVERSE makes it possible. I would consider this to be a good candidate for a function if you actually need to do this in more than one spot. Egads its ugly looking.
SELECT SUBSTRING(NAME,1, CHARINDEX(',', NAME)-1) as LastName,
LTRIM(SUBSTRING(NAME, CHARINDEX(',', NAME) + 1, LEN(NAME) - CHARINDEX(' ', REVERSE(NAME)) - CHARINDEX(',', NAME))) as FirstName,
RIGHT( SUBSTRING(REVERSE(NAME), 1, CHARINDEX(' ', REVERSE(NAME))-1), 1) AS MI
FROM maystaff
-
There are lots of person name forms. In addition to the standard first name/last name, first name/middle initial/last name, and first name/middle name/last name, there are also:
(1) First initial/middle name/last name
(2) Only one name. (I'm talking about ordinary people, not celebrity stage names.)
(3) Multiple middle names.
(4) Jr., Sr., III, and so on
Yes, these are legal names - they're on passports, driver's licenses, and so on.
In some circumstances, various titles, affiliations, status are also part of the name, such USMC, USMC, Ret (at the end) and "Lt" etc at the front.
And, the "last" name is not necessarily the "family name" or the name that you'd prepend with "Mr" - in some cultures, that's the first name. And, some of these folks have dealt with systems that don't understand this before so they'll use both orders even when dealing with the same organization. (They can't be expected to remember if your organization is one that they have to adjust to by reversing their name.)
-
Thanks for the help. I know this kind of stuff isn't much fun. The query you posted almost does it with one problem. It bombs out at the first record that has no Middle Initial. Can I build in a contingency so it won't bomb out? thanks
-
Here's a crude function I use alot. (Posted as a script contribution this AM):
E.G. select dbo.string_field( namecolumn, ' ', 1 ) AS first_name, dbo.string_field( namecolumn, ' ', 2 ) AS middle_name,...
Use case statements counting internal spaces to determine how many words are in the string to find out if there is a middle name...
(I have another funcion I can't find now that encapsulates the counting of characters and one that counts "fields" based on delimiters.)
DROP FUNCTION string_field
GO
CREATE FUNCTION string_field
( @string VARCHAR(4000) = NULL
,@delimiter VARCHAR(20)
,@position INT
) RETURNS VARCHAR(255)
AS
BEGIN
---NOTE: modified because prior version didn't handle space as delimiter
DECLARE @result VARCHAR(255)
,@work VARCHAR(4000)
,@pattern VARCHAR(255)
,@i INT
,@j INT
,@ld INT
SELECT @result = ""
,@ld = LEN( REPLACE( @delimiter, ' ', 'X' ) ) --Incase delimiter is a space LEN will evaluate to zero
--and we don't want that.
IF @position > 0 BEGIN
SELECT @pattern = "%" + @delimiter + "%"
,@work = @string
,@j = 0 --init
WHILE ( @j-2 < @position ) BEGIN
SELECT @i = PATINDEX( @pattern, @work )
,@j = @j-2 + 1
IF @i > 0 BEGIN
SELECT @result = SUBSTRING( @work, 1, @i - 1 )
,@work = SUBSTRING( @work, @i + @Ld, 4000 )
END ELSE BEGIN
IF @j-2 = @position
SELECT @result = @work
ELSE
SELECT @result = ""
,@j = @position
END
END
END
RETURN (@result)
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply