May 20, 2004 at 3:28 pm
I need to take full name (with middle initial sometimes) and parse into first and last name.
May 20, 2004 at 5:24 pm
My assumption would be that last name is everything after the last space in the name. first Name is everything before. I've done it pleanty of times and it has the least clean up afterward.
You can loop through in a cursor read in the name then loop backwards and find the last space. Then do a substring to parse out first and last, thats my old clunky method.
Or you can do it all in a sql statement. I do the same but I use the seldom used reverse function, get the first space (which was the last until I flipped the field). Then I do a substring from 1 to then len less the number of spaces of the last space from the end. Then that number + 1 to end for last. It works great. Hope this helps:
select
Substring(claimantLastName, 1, len(rtrim(claimantlastname)) - charindex(' ', Reverse(RTrim(ClaimantLastName)))) as FirstName,
rtrim(ltrim(Substring(claimantLastName, len(rtrim(claimantlastname)) - charindex(' ', Reverse(RTrim(ClaimantLastName))) + 1, len(rtrim(claimantlastname))))) from xiraclaims
(newbie my eye)
May 21, 2004 at 6:27 am
David,
If Edward is correct and all you really want is to split the last name from the rest and we assume that the last name will appear after the last space in the full name, then the following will work without a cursor (just for the sake of speed)... I setup a full test for you, including table creation and population, so you can "play"...
/****** Object: Table dbo.CustomerNameTest Script Date: 05/21/2004 08:17:40 AM ******/
if exists (select * from sysobjects
where id = object_id('dbo.CustomerNameTest')
and sysstat & 0xf = 3)
drop table dbo.CustomerNameTest
CREATE TABLE dbo.CustomerNameTest
(
CustID int IDENTITY (1, 1) NOT NULL ,
FullName varchar (50) NULL
)
INSERT INTO CustomerNameTest (FullName) VALUES ('John Smith')
INSERT INTO CustomerNameTest (FullName) VALUES ('John T Smith')
INSERT INTO CustomerNameTest (FullName) VALUES ('John T. Smith')
INSERT INTO CustomerNameTest (FullName) VALUES ('Mr. John T. Smith')
SELECT
FullName,
LEFT(FullName,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName))) AS FirstName,
RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName))-1) AS LastName
FROM CustomerNameTest
If you also need to split-out the "stuff" in the middle, we'll have to make a few more assumptions but post back and we'll see what we can do.
The example I gave looks similar in many areas as Ed's but I used LEFT and RIGHT instead of substring. If the names are not "clean" of leading and trailing spaces, you'll have to do what Ed did with the LTRIM RTRIM thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2004 at 10:23 am
Yes I need to remove the middle initial as well. Thanks.
May 23, 2004 at 8:59 pm
This should do it with some assumptions... the assumptions are that you will have no title (Mr., Mrs., etc) and no suffic (Jr. Sr. II, III, IV, etc).
/****** Object: Table dbo.CustomerNameTest Script Date: 05/21/2004 08:17:40 AM ******/
if exists (select * from sysobjects
where id = object_id('dbo.CustomerNameTest')
and sysstat & 0xf = 3)
drop table dbo.CustomerNameTest
go
CREATE TABLE dbo.CustomerNameTest
(
CustID int IDENTITY (1, 1) NOT NULL ,
FullName varchar (50) NULL
)
go
INSERT INTO CustomerNameTest (FullName) VALUES ('John Smith')
INSERT INTO CustomerNameTest (FullName) VALUES ('John T Smith')
INSERT INTO CustomerNameTest (FullName) VALUES ('John T. Smith')
SELECT
FullName,
LEFT(FullName,CHARINDEX(' ',FullName)-1) AS FirstName,
RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName))-1) AS LastName
FROM CustomerNameTest
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2004 at 8:34 pm
Jeff,
what if there are some prefixes and suffixes in the column? I'm facing this dilemma right now. What would I add to your script to successfully parse prefix, first, middle, last, suffix?
Any help is greatly appreciated.
June 29, 2004 at 1:31 pm
Whenever I have a weirdo situation, I write a UDF to handle it.
this will get you started. I wrote it up in a few minutes, so I didn't cover everything. That's up to you . And I didn't error proof it either. But as I said, it will get you thinking because you can see that I can "fix" the data anyway I want to... not bound by tsql rules.
I actually do this myself. I have a list of Schools I deal with,and sometimes when (teachers) report their school name, they fluff the name. Like my table will say "King Elementary" and they will put "King Elem".
I wrote (the same kind of proc below) to equate the 2 values ("Elem" and "Elementary" are treated the same).
Be careful. If you name has the letters "mr" in it, you wipe those out. You'll have to put advanced logic in to see "if 'mr' appears in the first 3 characters of the string", stuff like that.
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_parse_full_name') and xtype = 'TF')
drop function dbo.fnc_10_parse_full_name
GO
CREATE FUNCTION dbo.fnc_10_parse_full_name( @fullName varchar(32) )
RETURNS @tablevalues TABLE
( lastName varchar(16) , firstName varchar(16) )
AS
BEGIN
declare @pos1 int
select @pos1 = 0
declare @pos2 int
select @pos2 = 0
declare @possibleFName varchar(16)
declare @possibleLName varchar(16)
--Get rid of a bunch of fluffers
select @fullName = REPLACE(@fullName, '.', '')
select @fullName = REPLACE(@fullName, ',', '')
select @fullName = REPLACE(@fullName, 'Mrs', '')
select @fullName = REPLACE(@fullName, 'Ms', '')
select @fullName = REPLACE(@fullName, 'Mr', '')
select @fullName = REPLACE(@fullName, 'Jr', '')
select @fullName = LTRIM((@fullName))
select @pos1 = CHARINDEX(' ', @fullName)
if @pos1 = 0
BEGIN
INSERT INTO @tablevalues values ( 'ERROR' , '*' + @fullName + '*' + convert(varchar(12) , @pos1) )
END
select @pos2 = CHARINDEX(' ', @fullName , @pos1 + 1)
select @possibleFName = LEFT(@fullName, @pos1)
select @possibleLName = RIGHT(@fullName, LEN(@fullName) - @pos2)
INSERT INTO @tablevalues values ( LTRIM(RTRIM( @possibleLName )) , LTRIM(RTRIM(@possibleFName)) )
RETURN
END
GO
-- select * from dbo.fnc_10_parse_full_name('john fddsddfa cee')
-- select * from dbo.fnc_10_parse_full_name('Mr. John Ford Mustang, III')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply