Post reply

How to strip out name into 3 separate fields - FName,Lname,MName?

  • January 16, 2006 at 9:34 am

    #170166

    Hi experts,

       How to seperate First Name, Last Name, and Middle Name into 3 columns?

    I have a [Name] column in a table, the name is like this 'BERARD,JAMES STEVEN' or 'MINGO,NINA', 'THOMAS JR,SYLVESTER', or 'RAMIREZ - CORONADO,JOSE'

    I need to load into another table with 3 columns [FName], [LName], [MName], how can I do this by T-SQL?

    Thank you.

  • January 16, 2006 at 10:26 am

    #614954

    How about: 

    insert #t2 (lname, fname, mname)
select 
LEFT(#t1.name, CHARINDEX(',',#t1.name)-1) as lname, 
CASE WHEN CHARINDEX(' ',REVERSE(#t1.name)) > 0 THEN RIGHT(#t1.name, CHARINDEX(' ',REVERSE(#t1.name))-1) END as mname,
CASE WHEN CHARINDEX(' ',REVERSE(#t1.name)) > 0 
  THEN SUBSTRING(#t1.name, CHARINDEX(',',#t1.name)+1, 
    LEN(#t1.name) - CHARINDEX(',',#t1.name) - CHARINDEX(' ',REVERSE(#t1.name))) 
  ELSE SUBSTRING(#t1.name, CHARINDEX(',',#t1.name)+1,100)
END as fname
from #t1

     



    Mark

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply