2 Replies Latest reply: May 15, 2014 9:58 AM by Mike Urbonas RSS

    remove character

    helpless _

      I have a record field similar to

      xx-xxxxxx-xx-xx

       

      Is there a way to remove the "-" to leave;

      xx xxxxxx xx xx?

        • remove character
          helpless _

          I have tried the lsplit and rsplit as follows;

          rsplit(recordno,1,"-",1)

          but the results are the same xx-xxxxx-xx-xx.

           

          I thought that Monarch did not include the delimiter when it breaks out each substring.

          • remove character
            Mike Urbonas

            Originally posted by bls:

            I have tried the lsplit and rsplit as follows;

            rsplit(recordno,1,"-",1)

            but the results are the same xx-xxxxx-xx-xx.

             

            I thought that Monarch did not include the delimiter when it breaks out each substring.[/b][/quote]You are on the right path, and you are right that LSPLIT and RSPLIT do not include the "split" character in the formula results.

             

            But...your formula: rsplit(recordno,1,"-",1) is not quite right, because it is telling Monarch to split the data into 1 field!  The data already is one field, so it is leaving your xx-xxxxx-xx-xx data as is.

             

            Instead, try this:

            LSPLIT(recordno,4,"-",1)

             

            which says "Split the recordno field into 4 parts based on the "-" character, and return the first part."  Now, your result should be just the first two x's of your recordno data, appearing before the first "-"

             

            Do a similar forumla for each "part", concatenating them togehter with a space, and you should get the result you want:

             

            LSPLIT(recordno,4,"-",1)" "

            LSPLIT(recordno,4,"-",2)" "

            LSPLIT(recordno,4,"-",3)" "

            LSPLIT(recordno,4,"-",4)

             

            Regards,

            Mike

             

            [size="1"][ October 25, 2002, 03:52 PM: Message edited by: Mike Urbonas ][/size]