Parse / Separate data in a field to different fields which are separated by Char(13)

Posted on 2011-04-29
Last Modified: 2012-05-11
SHIP TO: JERRY DOE 1258 Academy Sq College Park,GA  30337

I need to separate to data stored in one field to multiple fields
1258 Academy Sq as Ship Address
College Park as ShipCity
GA as ShipState
30337 as Zip

The name , address and zip are separated by Char(13) and the city and state by ,
Question by:tvae
    LVL 9

    Accepted Solution

    First split the string on "," using below function

    Then Use CHARINDEX to get the data based on count.

    Author Comment

    I am not so sure how to use the CHARINDEX. I tried that and was only successful to pull the first one but not sure how to grab the next group
    LVL 9

    Expert Comment

    after first one you will get 2 strings like below.

    SHIP TO: JERRY DOE 1258 Academy Sq College Park
    GA  30337

    Again, Split the first string using CHARINDEX (Since u said that its CHAR(13)) and split the second string usng SPACE.

    Refer the thread in my previous post.


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    This video discusses moving either the default database or any database to a new volume.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now