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:





      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.




          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))


            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.

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



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

              This is actually a good idea, thanks!