Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Break up full name col into fname, lname cols Expand / Collapse
Author
Message
Posted Wednesday, November 12, 2003 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 17, 2004 6:23 AM
Points: 45, Visits: 1
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.




Post #18145
Posted Wednesday, November 12, 2003 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
Can you post some sample data?

Frank
http://www.insidesql.de
http://www.familienzirkus.de


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #86761
Posted Wednesday, November 12, 2003 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 17, 2004 6:23 AM
Points: 45, Visits: 1
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





Post #86762
Posted Wednesday, November 12, 2003 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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
http://www.insidesql.de
http://www.familienzirkus.de


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #86763
Posted Wednesday, November 12, 2003 9:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, Visits: 54
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
Post #86764
Posted Wednesday, November 12, 2003 12:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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
http://www.insidesql.de
http://www.familienzirkus.de


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #86765
Posted Wednesday, November 12, 2003 12:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
...btw, sorry to the original poster!

I wasn't aware of the PARSENAME function.
Sounds really handy for such situations.

Frank
http://www.insidesql.de
http://www.familienzirkus.de


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #86766
Posted Wednesday, November 12, 2003 1:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, Visits: 54
quote:

...btw, sorry to the original poster!

I wasn't aware of the PARSENAME function.
Sounds really handy for such situations.

Frank
http://www.insidesql.de
http://www.familienzirkus.de



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
Post #86767
Posted Thursday, November 13, 2003 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:19 AM
Points: 264, Visits: 189
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





Post #86768
Posted Thursday, November 13, 2003 6:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 23, 2007 4:46 AM
Points: 56, Visits: 1
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...




Post #86769
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse