break up a NAME col into FNAME, LNAME cols

• scotttr

  SSC Eights!

  Points: 965

  More actions

  November 12, 2003 at 8:19 am

  #106754

  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.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 12, 2003 at 8:53 am

  #481719

  ??

  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/[/url]

• Mark_maddison

  Right there with Babe

  Points: 768

  More actions

  November 12, 2003 at 2:45 pm

  #481777

  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?

• Linata

  Ten Centuries

  Points: 1370

  More actions

  November 13, 2003 at 4:23 am

  #481819

  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))

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 13, 2003 at 4:36 am

  #481821

  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/[/url]