3 Replies Latest reply on Jul 27, 2021 6:59 AM by Anna Amorim

    Remove data from string

    Anna Amorim New Member

      Hi, I have the following set of data:

       

      a) ["80000000~82999999","16115"]

      b) ["16113","80000000~82999999"]

       

      I need to remove anything that comes right before or after the character ~. In this example, the result should look like this:

       

      ["16115"]

      ["16113"]

       

      Is there any simple way to achieve this without using Java transformation?

        • 1. Re: Remove data from string
          user186817 Guru

          Hi Anna,

           

          If your source data has always this format, a simple approach may be:

           

          1. In an Expression transformation, create a variable port to keep all characters before the coma
          2. Create a second variable to keep all characters after the coma
          3. Create an output port to keep the shortest variable

           

          Please let me know if you need help building these expressions.

           

          Regards,

          Lluís

          1 of 1 people found this helpful
          • 2. Re: Remove data from string
            JanLeendert Wijkhuijs Guru

            Hi Anna,

            Is the source a flatfile?
            Then I would define two columns for which the comma is the separator.

            If this is coming from another type of source and it is just a string I would indeed split the content on the comma into 2 variable ports in an expression and remove the double quotes.
            To split the data you can use logic like

            SUBSTR(FIELD,1, (INSTR(FIELD,',',1,1)-1))

            and

            SUBSTR(FIELD,(INSTR(FIELD,',',1,1)+1),(LENGTH(FIELD) - INSTR(FIELD,',',1,1)))

             

            When you also remove the double quotes you can use testing IS_NUMBER() to check whether it is a number or a variable also containing other characters.

            The number value must be passed via an output port.

            Note:
            Longest or shortest string is not a good test if you don't know the ranges of your data.
            Better safe than sorry.

            Regards

            JanLeendert

            1 of 1 people found this helpful
            • 3. Re: Remove data from string
              Anna Amorim New Member

              This is actually a good idea, thanks!