Home Forums SQL Server 2008 SQL Server Newbies Split a name on to new columns in a table RE: Split a name on to new columns in a table

  • August 28, 2012 at 2:23 am

    #1530620

    amarkhowe (8/28/2012)

    Monring,

    If I have a name all in one cell on a column such as

    "Mr Simon ASHWORTH"

    How can I split the name so that I could have Mr on a column called title, Simon on a column called Fist Name and ASHWORTH on a column called Last Name?

    Many Thanks,

    Andy

    DROP TABLE #NamesAndAddresses

    CREATE TABLE #NamesAndAddresses (Fullname VARCHAR(100))

    INSERT INTO #NamesAndAddresses (Fullname) VALUES ('Mr Simon ASHWORTH')

    SELECT

    Title= LEFT(Fullname,space1.pos-1),

    Forename= SUBSTRING(Fullname,space1.pos+1,space2.pos-space1.pos-1),

    Surname= SUBSTRING(Fullname,space2.pos+1,8000)

    FROM #NamesAndAddresses

    CROSS APPLY (SELECT CHARINDEX(' ', Fullname,1)) space1 (pos)

    CROSS APPLY (SELECT CHARINDEX(' ', Fullname,space1.pos+1)) space2 (pos)

    Edit: slight change to code.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden