parse a column into several in SQL

Posted on 2010-01-06
Last Modified: 2012-05-08

I have a sql 2000 table that has a column with values such as

First Name - George Last Name - Smith Room number - 229 Chart Location
- 7FRH.2
First Name - Mark Last Name - Johnson Room number - 976 Chart Location
- 4FV.5
First Name - Mary Last Name - Wesley Room number - 224 Chart Location
- 8BSS.3
First Name - Lucia Last Name - Guess Room number - 453 Chart Location
- 6BF.1
First Name - Charlotte Last Name - Lopez Room number - 229 Chart
Location - 4VW.5

I need to parse these values into columns.  From these columns I only
need the actual data and not the delimiters

the delimeters are:

"First Name - "
" Last Name - "
" Room number - "
" Chart Location - "

Therefore, I need to get 4 columns:

George  Smith   229     7FRH.2
Mark    Johnson 976     4FV.5
and so on..

Any ideas on how to parse these values into the 4 columns I need?

Question by:TheUndecider
    LVL 75

    Accepted Solution

    SELECT SUBSTRING( ColumnName , charindex('-', ColumnName)+1, charindex('Last', ColumnName)-charindex('-', ColumnName)-1 ) as FistName,
           SUBSTRING( ColumnName , charindex('Last Name -', ColumnName)+11, charindex('Room', ColumnName)-charindex('Last Name -', ColumnName)-11 ) as LastName,
           SUBSTRING( ColumnName , charindex('Room number -', ColumnName)+13, charindex('Chart Location -', ColumnName)-charindex('Room number -', ColumnName)-13 ) as RoomNumber,
           RIGHT( ColumnName , LEN(ColumnName) - charindex('Chart Location -', ColumnName)-16 )ChartLocation
    FROM yourTable
    LVL 26

    Assisted Solution

    here is a sample... you just replace the @var with your column Name...
    declare @var varchar(1000)
    set @var = 'First Name - George Last Name - Smith Room number - 229 Chart Location - 7FRH.2'
    SELECT CHARINDEX(' Last Name - ', @var)
    SELECT SUBSTRING(@var, 13, CHARINDEX(' Last Name - ', @var) - 13) AS firstName,
    	   SUBSTRING(@var, CHARINDEX(' Last Name - ', @var) + 13, CHARINDEX(' Room number - ', @var) -(CHARINDEX(' Last Name - ', @var) + 13)) AS laststName, 
    	   SUBSTRING(@var, CHARINDEX(' Room number - ', @var) + 15, CHARINDEX(' Chart Location - ', @var) -(CHARINDEX(' Room number - ', @var) + 15)) AS roomNumber, 
    	   SUBSTRING(@var, CHARINDEX(' Chart Location - ', @var) + 18, LEN(@var) -(CHARINDEX(' Chart Location - ', @var) + 17)) AS charLocation

    Open in new window

    LVL 11

    Expert Comment


       You can SSIS package to do this

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    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.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    730 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

    16 Experts available now in Live!

    Get 1:1 Help Now