Break up full name col into fname, lname cols
-
I have a NAME field that consistes of a person's full name (first, middle, last) separated by spaces. I want to separate the NAME field into FNAME, LNAME, and MNAME fields. My problem is that the names are of variable lengths so I can't use the right, left, or substring functions. I know there is a way of doing it but I haven't figured it out yet. Thanks for the help.
-
Can you post some sample data?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sure, below is the field heading and some sample data.
INDNAME
----------------------
ROSE DONALD BRUCE
GAMACHE RICHARD HERVE
CARLSON DAVID ROBERT
HOWARD GARY WAYNE
GARNETT FREDERICK H
-
Should not be too difficult if there is always a blank as delimiter.
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17517 might give you some ideas
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This may seem easy, but is rife with potential problems. My last name is two words...
If all your data is indeed three strings delimited by single spaces, you could just try something like this:
UPDATE YourTable
SET LName = PARSENAME(REPLACE(LTRIM(RTRIM(IndName)),' ','.'),3),
FName = PARSENAME(REPLACE(LTRIM(RTRIM(IndName)),' ','.'),2),
MName = PARSENAME(REPLACE(LTRIM(RTRIM(IndName)),' ','.'),1)--Jonathan
--Jonathan -
quote:
This may seem easy, but is rife with potential problems. My last name is two words......and to complicate it a little bit, I guess you don't have a middle name, right?
Anyway, the original error was to store these informations in a single column. And, of course, it will leave always some rest of work for you to do, but it's easier once the data is in separate columns.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
...btw, sorry to the original poster!
I wasn't aware of the PARSENAME function.
Sounds really handy for such situations.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
...btw, sorry to the original poster!I wasn't aware of the PARSENAME function.
Sounds really handy for such situations.
Frank
Yes. I was surprised to see a script someone had posted for a function to split up ip addresses stored as strings; PARSENAME makes that rather easy.
--Jonathan
--Jonathan -
This might be useful...
declare @fullname varchar(150),
@space1 int,
@space2 int,
@first varchar(50),
@middle varchar(50),
@Last varchar(50)
set @fullname = 'first last'
select @space1 = CHARINDEX(' ',@fullname)
select @space2 = CHARINDEX(' ',@fullname,@space1+1)
select @first = left(@fullname,@space1-1)
if @space2 > 0
BEGIN
select @Last = right(@fullname,len(@fullname)-@space2)
select @middle = substring(@fullname,@space1+1,@space2-@space1-1)
END
ELSE
BEGIN
select @Last = right(@fullname,len(@fullname)-@space1)
select @middle = ''
END
SELECT @first as FirstName,
@middle as MiddleName,
@Last as LastName
-
Looking up parsename in help doesn't look like it applies to this topic. It looks like it is more concerned with breaking up a multipart server object name i.e. server.database.owner.table.column...
-
PARSENAME looks cool for IP ADDRESSES and for this particular case may be useful it is an example on how to use a simple function for uninteded purposes!
* Noel -
Hey Jonathan,
ThE PARSENAME() solution looks good. I'm validating the results. Thanks everyone for the contributions.
-
Here is some code I have used in the past:
Update Names
Set FullName = RTRIM(FullName)
Update Names
Set FullName = LTRIM(FullName)
Update Names
Set FullName = REPLACE(FullName, ' ', '!')
Where FullName LIKE '% %'
Select FullName as FullName_Original, FullName, CHARINDEX('!', FullName, 1) as Posititon
into #Temp
From Names
Select FullName, Left(FullName,Posititon)
as Name1
Into #Temp2
From #Temp
Update #Temp
Set FullName = REPLACE(#Temp.FullName, #Temp2.Name1, '')
From #Temp, #Temp2
Where #Temp.FullName = #Temp2.FullName
Update #Temp2
Set Name1 = REPLACE(Name1, '!', '')
Where Right(Name1,1) = '!'
Update Names
Set Names.FirstName = #Temp2.Name1
From Names, #Temp2
Where Names.FullName = #Temp2.FullName
Update Names
Set FullName = REPLACE(FullName, '!', ' ')
Where FullName LIKE '%!%'
Drop Table #Temp
Drop Table #Temp2
Then just repeat the steps for Name2 & Name3
-
You don't say anything about the quality of the name fields. If they're clean, the PARSENAME function is a great approach.
I work with a lot of crappy name & address info we get from clients, and I would be careful assuming it doesn't already have periods that would screw up the PARSENAME approach. Any "Mr." or "Mrs." or "Dr." prefixes in these names?
I've used LEFT(xx,CHARINDEX(' ',xx)-1) to get the first word in a text field. I've also used RIGHT(xx,CHARINDEX(' ',REVERSE(xx))-1) to get the last word. Use "WHERE CHARINDEX(' ',xx) > 0' if you're not sure there is more than one word in the field.
(Mostly to build tables to see what new prefixes and suffixes they've come up with this month)
-
For this type of problem I typically use a UDF. The one I'm providing here will return a table for each record. If you are only using it for a specific purpose you could certainly modify it to just return the full word for a given location. IE: 1st, 2nd, or last word.
CREATE FUNCTION dbo.f_ParseDelimitedList
(
@ID int -- Used so we can link the resulting table in a query to some value
, @delimitedList nvarchar(3000) -- The list of items to parse out.
, @Delimiter nchar(1) = ',' -- The delimiter used. Defaults to a comma.
)
RETURNS @tbl TABLE (ID int ,ObjID int , FieldValue nvarchar(260))
BEGIN
DECLARE
@CounterID nvarchar(4)
, @FieldValue nvarchar(260)
DECLARE @tmpTable TABLE (ID int IDENTITY(1,1),ObjID int , FieldValue nvarchar(260))
WHILE CharIndex(@Delimiter, @delimitedList) > 0
BEGIN
SET @FieldValue = LTrim(RTrim(SUBSTRING(@delimitedList, 1, charIndex(@Delimiter, @delimitedList)-1)))
INSERT INTO @tmpTable (ObjID, FieldValue)
SELECT @ID, @FieldValue
SET @delimitedList = LTrim(RTrim(SUBSTRING(@delimitedList, (charIndex(@Delimiter, @delimitedList) + 1), Len(@delimitedList))))
END
IF LTrim(RTrim(@delimitedList)) != ''
INSERT INTO @tmpTable (ObjID, FieldValue)
SELECT @ID, @delimitedList
INSERT INTO @tbl SELECT * FROM @tmpTable
RETURN
END
/* Usage
Select *
From dbo.f_ParseDelimitedList(1,'Apples,Oranges',',')
*/
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Edited by - gljjr on 11/17/2003 7:23:51 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply