none
Spilt the data into columns RRS feed

  • Question

  • hi ,

     How to spilt the below Data into separate column is delimited with SINGLE PERIOD. If the data is not available between the periods I want the NULL to displayed.

    INDIA.CHENNAI.GUNIDY.

    INDIA.BANGALORE..

    INDIA...

    Thursday, January 8, 2015 11:24 AM

Answers

  • Well that was an ordeal. Apparently MSDN does not like your words.

    DECLARE @table TABLE (myString VARCHAR(1000), stringSeq INT IDENTITY)
    
    INSERT INTO @table (myString) VALUES ('something.somethingelse.thirdthing.'),('one.two..'),('pie...')
     
    ;WITH rCTE AS(
     SELECT myString, CAST(NULL as VARCHAR(1000)) AS word, mystring as remainingString, 0 AS wordSeq, stringSeq
       FROM @table
     UNION ALL
     SELECT myString, NULLIF(LEFT(remainingString,CHARINDEX('.',remainingString)-1),''), RIGHT(remainingString,LEN(remainingString)-CHARINDEX('.',remainingString)), wordSeq+1, stringSeq
       FROM rCTE
      WHERE CHARINDEX('.',remainingString) > 0
     )
     
    SELECT * FROM (
     SELECT word, stringSeq, wordSeq
       FROM rCTE
      WHERE wordSeq > 0
      ) s
     PIVOT (
            MAX(word) for wordSeq IN ([1],[2],[3],[4])
        ) p
     
    
    
    
    
    

    Thursday, January 8, 2015 2:50 PM

All replies

  • Here you go.. you'll need to know the maximum number of words ahead of time, though:

    DECLARE @table TABLE (myString VARCHAR(1000), stringSeq INT IDENTITY)
    INSERT INTO @table (myString) VALUES ('INDIA.CHENNAI.GUNIDY.'),('INDIA.BANGALORE..'),('INDIA...')
    
    ;WITH rCTE AS(
    SELECT myString, CAST(NULL as VARCHAR(1000)) AS word, mystring as remainingString, 0 AS wordSeq, stringSeq
      FROM @table
    UNION ALL
    SELECT myString, NULLIF(LEFT(remainingString,CHARINDEX('.',remainingString)-1),''), RIGHT(remainingString,LEN(remainingString)-CHARINDEX('.',remainingString)), wordSeq+1, stringSeq
      FROM rCTE
     WHERE CHARINDEX('.',remainingString) > 0
    )
    
    SELECT * FROM (
    SELECT word, stringSeq, wordSeq
      FROM rCTE
     WHERE wordSeq > 0
     ) s
    PIVOT (
           MAX(word) for wordSeq IN ([1],[2],[3],[4])
    	  ) p

    Thursday, January 8, 2015 2:45 PM
  • You'll need to know the maximum number of columns, but here you go:

    DECLARE @table TABLE (myString VARCHAR(1000), stringSeq INT IDENTITY)
    INSERT INTO @table (myString) VALUES ('INDIA.CHENNAI.GUNIDY.'),('INDIA.BANGALORE..'),('INDIA...')
    
    ;WITH rCTE AS(
    SELECT myString, CAST(NULL as VARCHAR(1000)) AS word, mystring as remainingString, 0 AS wordSeq, stringSeq
      FROM @table
    UNION ALL
    SELECT myString, NULLIF(LEFT(remainingString,CHARINDEX('.',remainingString)-1),''), RIGHT(remainingString,LEN(remainingString)-CHARINDEX('.',remainingString)), wordSeq+1, stringSeq
      FROM rCTE
     WHERE CHARINDEX('.',remainingString) > 0
    )
    
    SELECT * FROM (
    SELECT word, stringSeq, wordSeq
      FROM rCTE
     WHERE wordSeq > 0
     --ORDER BY stringSeq, wordSeq
     ) s
    PIVOT (
           MAX(word) for wordSeq IN ([1],[2],[3],[4])
    	  ) p

    Thursday, January 8, 2015 2:47 PM
  • Well that was an ordeal. Apparently MSDN does not like your words.

    DECLARE @table TABLE (myString VARCHAR(1000), stringSeq INT IDENTITY)
    
    INSERT INTO @table (myString) VALUES ('something.somethingelse.thirdthing.'),('one.two..'),('pie...')
     
    ;WITH rCTE AS(
     SELECT myString, CAST(NULL as VARCHAR(1000)) AS word, mystring as remainingString, 0 AS wordSeq, stringSeq
       FROM @table
     UNION ALL
     SELECT myString, NULLIF(LEFT(remainingString,CHARINDEX('.',remainingString)-1),''), RIGHT(remainingString,LEN(remainingString)-CHARINDEX('.',remainingString)), wordSeq+1, stringSeq
       FROM rCTE
      WHERE CHARINDEX('.',remainingString) > 0
     )
     
    SELECT * FROM (
     SELECT word, stringSeq, wordSeq
       FROM rCTE
      WHERE wordSeq > 0
      ) s
     PIVOT (
            MAX(word) for wordSeq IN ([1],[2],[3],[4])
        ) p
     
    
    
    
    
    

    Thursday, January 8, 2015 2:50 PM
  • this?

    DECLARE @Str varchar(1000) = 'india..'
    
    select NULLIF(Val,'')
    FROM dbo.ParseValues(@Str,'.')f
    
    

    If its in a table column use

    SELECT NULLIF(Val,'')
    FROM tablename t
    CROSS APPLY dbo.ParseValues(t.ColumnName,'.')f


    The UDF can be found here

    http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Thursday, January 8, 2015 3:11 PM
    Moderator
  • OP was asking for columns, rather than rows.
    Thursday, January 8, 2015 3:32 PM
  • OP was asking for columns, rather than rows.
    Patrick, I could not understand your post, please.
    Thursday, January 8, 2015 3:38 PM
    Answerer
  • The spam filter has been made more active and since OP used names of cities in BOLD I guess spam filter did not liked it. Everything is OK now I guess

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, January 8, 2015 4:02 PM
    Moderator
  • Hello ,

    Read the doc : "Five methods converting rows to columns "


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, January 11, 2015 7:14 AM