none
How to get First Name and Last Name From Full_Name with comma separated

    Question

  • One more question for the week, if possible quick response from you guys to wrap up my project by tomorrow.  

    I have Full names lists separated by comma and there are some spaces at the end look the following sample code; I want to change to first name and last name columns.

    I have this now:

    IF EXISTS(SELECT * FROM sys.objects WHERE NAME ='MyTable' AND TYPE ='U')  
    DROP TABLE MyTable  
    GO  
    CREATE TABLE MyTable (  
    FULL_Name Varchar(50) NULL 
    )  
    INSERT INTO dbo.MyTable VALUES('John,Smith    ')  
    INSERT INTO dbo.MyTable VALUES('Kirk,Jon   ')  
    INSERT INTO dbo.MyTable VALUES('Cooper,Mike      ')  
    INSERT INTO dbo.MyTable VALUES('Robert,Chester  ')  
    INSERT INTO dbo.MyTable VALUES('Patrick,Rick       ')  
    INSERT INTO dbo.MyTable VALUES('Peter, Matt    ')  
    ---  
    SELECT * FROM dbo.MyTable 



    I want it to be like this:

    IF EXISTS(SELECT * FROM sys.objects WHERE NAME ='MyTable' AND TYPE ='U')  
    DROP TABLE MyTable  
    GO  
    CREATE TABLE MyTable (  
    FULL_Name Varchar(50) NULL,  
    First_Name Varchar(50) NULL,  
    Last_Name Varchar(50) NULL 
    )  
    INSERT INTO dbo.MyTable VALUES('John,Smith    ','Smith','John')  
    INSERT INTO dbo.MyTable VALUES('Kirk,Jon   ','Jon','Kirk')  
    INSERT INTO dbo.MyTable VALUES('Cooper,Mike      ','Mike','Cooper')  
    INSERT INTO dbo.MyTable VALUES('Robert,Chester  ','Chester','Robert')  
    INSERT INTO dbo.MyTable VALUES('Patrick,Rick       ','Rick','Patrick')  
    INSERT INTO dbo.MyTable VALUES('Peter, Matt    ','Matt','Peter')  
    --  
    SELECT * FROM dbo.MyTable 


    Thank you very much for your help.

    Sami
    Thursday, March 05, 2009 7:37 PM

Answers

  •  
    select   
      FullName,  
      left(FullName,charindex(',',FullName)-1) as FirstName,  
      substring(FullName,charindex(',',FullName)+1,len(FullName)) as LastName  
    from 
      MyTable  
     

    --Brad
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 7:47 PM
    Moderator
  • Do something like this after you inserted the data for Full_name

    Update mytable  
    set Last_name=name   
    from (select full_name, substring(full_name,charindex(',',full_name)+1,len(full_name))name from mytable)as b  inner join mytable a on  
     
      a.full_nameb.full_name 
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 7:50 PM
  • If the length of the column is 50, and you are sure that just one comma is present, then you can use function parsename.

    SELECT  
        full_name,  
        RTRIM(LTRIM(PARSENAME(REPLACE(full_name, ',', '.'), 2))) AS FirstName,    
        RTRIM(LTRIM(PARSENAME(REPLACE(full_name, ',', '.'), 1))) AS LastName  
    FROM  
        MyTable;  
    GO 

    You can also use string functions like CHARINDEX, PATINDEX, SUBSTRING, LEFT, RIGHT, RTRIM, LTRIM, ETC.

    SELECT  
        full_name,  
        LEFT(full_name, CHARINDEX(',', full_name, 1) - 1) AS FirstName,   
        RTRIM(LTRIM(STUFF(full_name, 1, CHARINDEX(',', full_name, 1), ''))) AS LastName  
    FROM  
        MyTable;  
    GO 


    Need to check if there is no comma in the value.


    AMB
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 8:00 PM
    Moderator

All replies

  •  
    select   
      FullName,  
      left(FullName,charindex(',',FullName)-1) as FirstName,  
      substring(FullName,charindex(',',FullName)+1,len(FullName)) as LastName  
    from 
      MyTable  
     

    --Brad
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 7:47 PM
    Moderator
  • Do something like this after you inserted the data for Full_name

    Update mytable  
    set Last_name=name   
    from (select full_name, substring(full_name,charindex(',',full_name)+1,len(full_name))name from mytable)as b  inner join mytable a on  
     
      a.full_nameb.full_name 
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 7:50 PM
  • If the length of the column is 50, and you are sure that just one comma is present, then you can use function parsename.

    SELECT  
        full_name,  
        RTRIM(LTRIM(PARSENAME(REPLACE(full_name, ',', '.'), 2))) AS FirstName,    
        RTRIM(LTRIM(PARSENAME(REPLACE(full_name, ',', '.'), 1))) AS LastName  
    FROM  
        MyTable;  
    GO 

    You can also use string functions like CHARINDEX, PATINDEX, SUBSTRING, LEFT, RIGHT, RTRIM, LTRIM, ETC.

    SELECT  
        full_name,  
        LEFT(full_name, CHARINDEX(',', full_name, 1) - 1) AS FirstName,   
        RTRIM(LTRIM(STUFF(full_name, 1, CHARINDEX(',', full_name, 1), ''))) AS LastName  
    FROM  
        MyTable;  
    GO 


    Need to check if there is no comma in the value.


    AMB
    • Marked as answer by SamiDC Friday, March 06, 2009 12:03 AM
    Thursday, March 05, 2009 8:00 PM
    Moderator
  • Thank you Every body, that's an awesome response, what you guys gave me is perfect. No words to express for your kind response. you make my day smile.

    Thank you,
    Sami
    Friday, March 06, 2009 12:03 AM