Post reply

Splitting a string into fields

  • March 6, 2013 at 8:46 am

    #269290

    Hi All

    I have a field called Defects which contain strings like the following. The string makeup is always the same.

    Scrap : Part Assembly : Surface Defects : Scratch

    Scrap : Part Assembly : Components : Wrong Components

    Scrap : Part Assembly : Other : Change Over

    Repair : Punching : Surface Damages : Crack

    What I would like to do is create a view that splits the string by the : and have them displayed as individual fields.

    I started of using CharIndex to find the : and then use left and the charindex to strip the first part out, but I felt doing it this way would involve a lot of LEFTs and RIGHTs to split up the whole string, is there an easier way to accomplish this?

    Cheers

    DJ

  • March 6, 2013 at 8:50 am

    #1594018

    Take a look at this article by Jeff Moden.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • March 6, 2013 at 9:05 am

    #1594025

    Hi

    Thanks for the reply, that whole post is a little above my head, but I will give it another read to see if I can make sense out of it.

    Cheers

    Dj

  • March 6, 2013 at 9:10 am

    #1594029

    If there really are always four elements, then you could use PARSENAME (look it up in BOL):

    ;WITH SampleData AS (

    SELECT MyString = 'Scrap : Part Assembly : Surface Defects : Scratch' UNION ALL

    SELECT 'Scrap : Part Assembly : Components : Wrong Components' UNION ALL

    SELECT 'Scrap : Part Assembly : Other : Change Over' UNION ALL

    SELECT 'Repair : Punching : Surface Damages : Crack')

    SELECT

    MyString,

    Element1 = PARSENAME(REPLACE(MyString,' : ','.'),4),

    Element2 = PARSENAME(REPLACE(MyString,' : ','.'),3),

    Element3 = PARSENAME(REPLACE(MyString,' : ','.'),2),

    Element4 = PARSENAME(REPLACE(MyString,' : ','.'),1)

    FROM SampleData

    “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

  • March 6, 2013 at 9:37 am

    #1594049

    That Parsename always sneaks up on me..

Viewing 5 posts - 1 through 4 (of 4 total)

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