Post reply

separating first and last name in one column delimited by a space into two columns

  • March 23, 2005 at 9:31 am

    #68805

    Hello,

    Currently I have a table where the first and last name are stored in one column. However I need to put the first name in a seperate column as well as the last name and set the name feild to null. The first and last name are delimited by a space so I presume that this can be done using a stored proc, however i'm going round in circles. Can anyone offer any light on the situation?

    thanks

    Sebastian

  • March 23, 2005 at 9:58 am

    #548537

    Here is a simple example of what you are describing:

    CREATE TABLE A_Table (FullName varchar(50),

                          FirstName varchar(25),

                          LastName varchar(25))

    INSERT INTO A_Table (FullName) VALUES ('Homer Simpson')

    INSERT INTO A_Table (FullName) VALUES ('F Flintstone')

    UPDATE A_Table

       SET FirstName = LEFT(FullName,CHARINDEX(' ',FullName) - 1),

           LastName = RIGHT(FullName,LEN(FullName) - CHARINDEX(' ',FullName))

    SELECT * FROM A_Table

    DROP TABLE A_Table

    The Update statement can easily be placed into a stored proc, or ran in Query Analyzer.  Whichever is more convenient or acceptable in your environment.

    Hope this helps

    Wayne

  • March 24, 2005 at 9:06 am

    #548788

    Wayne's solution is fine, just be very careful and make a thorough check of the results before you allow this update in production. There could be various problems caused by irregular names, typing errors or incompatible entries.. a few examples:

    'John F. Kennedy', 'Churchill Winston', 'lord Peter Wimsey', 'Prof. Albert Einstein', ' John Doe'

    You can never rely on users that they follow all rules, quite often the data are in a rather bad shape for such operations. Good luck!

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

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