Home Forums SQL Server 2008 SQL Server Newbies Splitting a string into fields RE: Splitting a string into fields

  • 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