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

break up a NAME col into FNAME, LNAME cols Expand / Collapse
Author
Message
Posted Wednesday, November 12, 2003 8:19 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 #18146
Posted Wednesday, November 12, 2003 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
??
See my answer in the T-SQL section

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 #86777
Posted Wednesday, November 12, 2003 2:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 20, 2004 1:21 PM
Points: 60, Visits: 1
Are you doing an Active X?

If so you could try spliting the string on the commas into an array.
arryTemp=split(NameField,",")

Any chance to have the file better formatted before it gets to you?




Post #86778
Posted Thursday, November 13, 2003 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 4, 2009 3:49 AM
Points: 176, Visits: 45
May be it wikk help
declare @str varchar(30)
set @str='First Middle Last'

select left (@str,CHARINDEX(' ',@str)),
substring(@str,CHARINDEX(' ',@str)+1, charindex (' ',@str,CHARINDEX(' ',@str)+1) - CHARINDEX(' ',@str)),
right( @str, len(@str) - charindex (' ',@str,CHARINDEX(' ',@str)+1))




Post #86779
Posted Thursday, November 13, 2003 4:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
This is a cross post!

I think Jonathan gave an excellent solution here
http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=18145&FORUM_ID=8&CAT_ID=1&Topic_Title=Break+up+full+name+col+into+fname%2C+lname+cols&Forum_Title=T%2DSQL

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 #86780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse