SQL Server - Help parsing name field

Posted on 2012-08-15
Last Modified: 2012-08-15
I need help parsing a NAME field into 2 fields

the NAME field data looks like this


I need it parsed into   a LASTNAME and FIRSTNAME FIELD

SMITH                  JOHN
DOE                     Jane
Question by:JElster
    1 Comment
    LVL 25

    Accepted Solution

    If LASTNAME and FIRSTNAME are the only things in the field and LASTNAME always starts at the beginning:
    with cte as (
    select 'LASTNAME : SMITH   FIRSTNAME:  JOHN' as name union all
    select 'LASTNAME : DOE   FIRSTNAME:  Jane')
    select name
         , rtrim(ltrim(substring(name, 11, CHARINDEX( 'FIRSTNAME:' , name )-11))) as lastname
         , rtrim(ltrim(substring(name, CHARINDEX( 'FIRSTNAME:' , name )+10,999))) as firstname
    from cte

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now