Post reply

help with sql query

  • July 9, 2012 at 9:33 am

    #258574

    I have a table with a column having data as below: I am looking to split the data of this column to separate columns

    part1 area 2 abc

    part10 area 10 def

    part134 area 11 dsd

    part1 area 3 tyu

    part1 area 5 pqr

    i need the first part in 1 column, ie part1,part10,part 134 etc

    the next part in next column area 2, area 10, area 11 etc

    and the third part in another column. any help is greatly appreciated. Thank you

  • July 9, 2012 at 9:56 am

    #1510419

    As long as you will never go beyond four or five columns, the following query works well. Otherwise Google delimitedsplit8k.

    -- sample data

    ;WITH SampleTable AS (

    SELECT Stringybit = 'part1 area 2 abc' UNION ALL

    SELECT 'part10 area 10 def' UNION ALL

    SELECT 'part134 area 11 dsd' UNION ALL

    SELECT 'part1 ar____ea 3 tyu' UNION ALL

    SELECT 'part1 area 5 pqr'

    )

    -- solution

    SELECT *,

    Word1 = LEFT(Stringybit,x1.p-1),

    Word2 = SUBSTRING(Stringybit, x1.p+1, x3.p-(x1.p+1)),

    Word3 = SUBSTRING(Stringybit,x3.p+1,8000)

    FROM SampleTable

    CROSS APPLY (SELECT p = CHARINDEX(' ',Stringybit,1)) x1 -- end of 'part1'/start of area

    CROSS APPLY (SELECT p = CHARINDEX(' ',Stringybit,x1.p+1)) x2

    CROSS APPLY (SELECT p = CHARINDEX(' ',Stringybit,x2.p+1)) x3

    “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

Viewing 2 posts - 1 through 1 (of 1 total)

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