8 Replies Latest reply on Feb 4, 2021 4:10 AM by user126898

    Split string with pipe delinter within quotes into separate values

    Dianna Gibbs Seasoned Veteran

      Hi - I have a project I've been working on following a thread Nico shared How to split delimited string in expression transformation?  several years ago.  I actually have a workflow using this technique but below I'm having issues.  Not sure what I'm missing?

       

      This is my expression below. 

      In column from FlatFile

      Add pipe/| before and after all values so I can count |/pipes

      Then decode to get the value.

       

      As you can see below, my goal is to take the first value in quotes (pipe delimited if one value or more than 1 value).

      I'm getting quotes/nulls if null value which I could strip out but when I have only one value, it repeats, same as two values repeats.  Only 3 values work correctly.  Could have up to five values.

       

      Can I add something like If Null ignore or write to another value and then send those values to a fake file?

      Or count somehow prior?

      Could I add an If Not Null in my first InStr?

       

      Really appreciate any advice and direction.

       

       

       

       

       

       

        • 1. Re: Split string with pipe delinter within quotes into separate values
          Nico Heinze Guru

          Hi Dianna,

           

          the only thing that comes to my mind OOTB is that you're using SUBSTR() with the positions of the pipe characters.

          Normally I would use SUBSTR() from <position of one pipe> + 1 up to and including <position of next pipe> - 1.

          But this has one disadvantage: it works only if there is always at least one character, otherwise you get empty strings instead of NULL values.

           

          Could you please provide one or two sample input values and the expected output?

           

          Thanks,

          Nico

          • 2. Re: Split string with pipe delinter within quotes into separate values
            Dianna Gibbs Seasoned Veteran

            Sorry, I thought I had posted outcome and the issues.  Below is what I want for output.  What is happening is my single values are duplicating and my two values are doing the same.  My nulls are writing ,"","","",.

             

             

            • 3. Re: Split string with pipe delinter within quotes into separate values
              Dianna Gibbs Seasoned Veteran

              This is my INCORRECT outcome from above transformation:

               

              • 4. Re: Split string with pipe delinter within quotes into separate values
                Nico Heinze Guru

                Thanks, now I found the problem.

                The root cause of trouble is that the approach with the five v_pipe_* variable ports requires that the input string does always contain five input fields. Which is not the case here. So in some cases the string variables are set to the "remainder" of the input string, and that's of course not correct.

                I would change the whole approach with the v_val* ports like this (this also will make sure that they never contain the pipe signs):
                First (as your input strings may contain up to five fields, meaning four delimiter pipes within the string itself plus to the two extra ones) set up one more variable port v_pipe_6:
                    InStr( in_AAS, '|', 1, 6)
                This makes the following steps a little easier.
                Next set up five more variable ports v_len_1 thru v_len_5 with these contents:
                v_len_1:
                    Decode( TRUE, v_pipe_2 > v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1, 0)

                v_len_2:

                    Decode( TRUE, v_pipe_3 > v_pipe_2 + 1, v_pipe_3 - v_pipe_2 - 1, 0)

                v_len_3:

                    Decode( TRUE, v_pipe_4 > v_pipe_3 + 1, v_pipe_4 - v_pipe_3 - 1, 0)

                v_len_4:

                    Decode( TRUE, v_pipe_5 > v_pipe_4 + 1, v_pipe_5 - v_pipe_4 - 1, 0)

                v_len_5:

                    Decode( TRUE, v_pipe_6 > v_pipe_5 + 1, v_pipe_6 - v_pipe_5 - 1, 0)

                 

                Now set the v_val_* ports as follows:

                v_val_1:
                    Decode( v_len_1, 0, '', SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))

                v_val_2:

                    Decode( v_len_2, 0, '', SubStr( in_AAS, v_pipe_2 + 1, v_pipe_3 - v_pipe_2 - 1))

                and so on until v_val_5:

                    Decode( v_len_5, 0, '', SubStr( in_AAS, v_pipe_5 + 1, v_pipe_6 - v_pipe_5 - 1))

                 

                That should work, but to be honest I haven't tried it myself, I'm writing this off the top of my head.

                 

                Regards,

                Nico

                • 5. Re: Split string with pipe delinter within quotes into separate values
                  Dianna Gibbs Seasoned Veteran

                  Thanks so much - will give it a try this morning.

                  • 6. Re: Split string with pipe delinter within quotes into separate values
                    Dianna Gibbs Seasoned Veteran

                    Thank you Nico - this is working perfect.  One last question.  I am writing file with quotes - but it's putting quotes around my null values in these columns - is this expected behavior?  Or can I strip them out someway?

                     

                    • 7. Re: Split string with pipe delinter within quotes into separate values
                      Nico Heinze Guru

                      Good question...

                      To be honest I'm not sure.

                      You might want to change the expression a bit, maybe that helps. If not, I can make another suggestion, but let's see them step by step.

                      For now I would change the expression terms for v_val* like this:

                       

                      Current expression (for example, v_val_1):

                         Decode( v_len_1, 0, '', SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))

                       

                      New expression term:

                         Decode( v_len_1, 0, NULL, SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))

                       

                      (meaning you replace the empty string with a NULL value).

                       

                      Again, I really don't know whether PowerCenter will enclose NULL strings in a target file with quotes; for empty strings it sure does, as we can see here.

                       

                      Regards,

                      Nico

                      • 8. Re: Split string with pipe delinter within quotes into separate values
                        user126898 Guru

                        Nico has the right fix.  PwC does NOT enclose NULL values in quotes but does empty strings ('').  So if you switch to the new expression he provided above, even if you configure the target to enclose the values in double strings, null values will not.

                         

                         

                        Thanks,

                        Scott

                        1 of 1 people found this helpful