7 Replies Latest reply on Mar 29, 2016 10:44 AM by swaroop.gantela

    Splitting String into separate strings.

    niranjan.solase.0

      we strings in data like

      Column1

      a^b^c

      a^b

      a^b^c^d

       

      we need to separate data into separate strings -

      a

      b

      c

      d

       

      Note - I can use Split but here Index is Not fixed. Sometimes I can have 5 '^' and sometimes 1 '^'.

        • 1. Re: Splitting String into separate strings.
          swaroop.gantela

          Niranjan,

           

          The specified item was not found. has an excellent step-by-step

          explanation on how to accomplish the split and pivot:

          Converting columns to multiple rows

          • 2. Re: Splitting String into separate strings.
            Suraj Shah

            Hi ,

             

            Try the Custom Split--> All option. See attached images.

             

            1.PNG2.PNG

             

            Suraj

            • 3. Re: Splitting String into separate strings.
              niranjan.solase.0

              Hey guyz,

               

              Solutions you guyz provided is working Although I am looking something automated. Like in this solutions I have to manually create Splitted Data, Pivot fields and then analyze it.

               

              Is there any solution where we can Automate all this ?

              • 4. Re: Splitting String into separate strings.
                swaroop.gantela

                Niranjan,

                 

                This may be closer to what you need:

                using Custom SQL as described in kettan 's The Cross Join Collection  Section #4

                • 5. Re: Splitting String into separate strings.
                  kettan

                  Thanks, Swaroop, for the reference to the The Cross Join Collection.  Appreciated!

                   

                  News:

                  A CROSS JOIN with Tableau's join dialog  performs much better than Custom SQL.

                  The attached is a modification of your attachment that cross joins with Tableau's built-in join dialog.

                   

                  Changes:

                  1)  dummy join keys are added (CrossJoinKey)

                  2)  formulas use Tableau syntax instead of SQL Jet (see 2a and 2b below)

                  3)  downgrade to 9.0 (because I don't have license to newer versions)

                   

                  2a) Filter

                   

                  [Pos] <= LEN([Column1]) AND MID('^' + [Column1], [Pos], 1) = '^'

                   

                  2b) Value

                   

                  MID([Column1], [Pos], FIND( [Column1] + '^', '^', [Pos]) - [Pos])

                   

                   

                  Attached Workbook Version:  9.0

                  .

                  • 6. Re: Splitting String into separate strings.
                    Ben Moss

                    If you wanted to use a series of calculated fields to complete the clean up then here is how you could do it...

                     

                    Column 1

                     

                    IF (LEN(REPLACE([String],"^",""))) = LEN([String])     (here I am testing to see if there are any '^' in our text string)

                     

                    THEN [String]    (if there are not any '^' in our text string then just return the text string)

                     

                    ELSE LEFT([String],FIND([String],"^",0)-1)     (if there are any '^' then we want to return the characters before the 1st occurrence of '^')

                     

                    END

                     

                    Now to find the 2nd part of the string...

                     

                    Column 2

                     

                    IF (LEN(REPLACE([String],"^",""))) = LEN([String])     (here I am testing to see if there are any '^' in our text string)

                     

                    THEN NULL     (if there are not any '^' in our text string then return NULL - there are no values for the 2nd part of our string)

                     

                    ELSEIF (LEN(REPLACE([String],"^",""))) = LEN([String])-1     (here I am testing if there is just one '^' in our text string)

                     

                    THEN MID([String],FIND([String],"^")+1)     (IF THERE IS JUST ONE '^' then here I am finding the position of the 1st '^' and extracting all text to the right of this)

                     

                    ELSE MID([String],FIND([String],"^")+1,FIND([String],"^",FIND([String],"^"))-1)     (where there is a 1st and 2nd '^' here I am extracting the text between the 1st occurence of '^' and the 2nd occurence of '^')

                     

                    END

                     

                    Now you just need to slightly modify the above formula for as many parts as you may have in the string...

                     

                    e.g. Column 3

                     

                    IF (LEN(REPLACE([String],"^",""))) = LEN([String])     (here I am testing to see if there are any '^' in our text string)

                     

                    then NULL     (if there are not any '^' in our text string then return NULL - there are no values for the 2nd part of our string)

                     

                    ELSEIF (LEN(REPLACE([String],"^",""))) = LEN([String])-1      (here I am testing to see if there are more than just one '^' in our text string)

                     

                    THEN NULL     (if there are not more than one ^' in our text string then return NULL - there are no values for the 3rd part of our string)

                     

                    ELSEIF (LEN(REPLACE([String],"^",""))) = LEN([String])-2     (here I am testing if there is just two '^' in our text string)

                     

                    then MID([String],FIND([String],"^",FIND([String],"^")+1)+1)    

                      (IF THERE IS JUST TWO '^' then here I am finding the position of the 2nd '^' and extracting all text to the right of this)

                     

                    ELSE MID([String],FIND([String],"^",FIND([String],"^")+1)+1,FIND([String],"^",FIND([String],"^",FIND([String],"^")))-1)   

                    (where there is a 2nd and 3rd '^' here I am extracting the text between the 2nd occurence of '^' and the 3rd occurence of '^')

                     

                    END

                     

                    Now you can continue to expand this statement to capture all 6 parts (5 possible ^'s) or more.

                    • 7. Re: Splitting String into separate strings.
                      swaroop.gantela

                      Johan,

                       

                      Outstanding. Very clean.

                      Thank you for the update.

                      And for all you do for the Community.