5 Replies Latest reply: Oct 16, 2012 9:50 AM by Stefan Wühl RSS

    Separate a String

    Liz Bauer

      Hello All!,

       

      I have a field called Option_Code whose value is a string that I'd like to separate out.

       

       

      Option_Code:

      0500_0599_VALUE_PKG

      9078_FENDER_LIGHTS

      9188_ENGINE_BLK_HTR_110VG

      0001_0249_DESTN_COUNTRY

       

      All I want is everything from the left of the text to be deleted so that it looks like this:

      VALUE_PKG

      FENDER_LIGHTS

      ENGINE_BLK_HTR_110VG

      DESTN_COUNTRY

       

       

       

      I'm assuming i'll need to use a trim function, but i'm not sure how the coding should be..

       

      I tried using

      =purgechar([OPTION BOX],'_')

      which helps get rid of the underscores '_'

      but i'd like to only have the TEXT showing ( not the beginning #'s)

       

      Thank you for any help!

      -Liz

        • Re: Separate a String
          Rebecca Molstad

          Try something like this:

           

          if(Left(Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1),1)='_',Right(PurgeChar(Value,'0123456789'),
          Len(PurgeChar(Value,'0123456789'))-2),Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1)) as Final

           

          See attached.

          • Re: Separate a String
            Stefan Wühl

            Hi Liz,

             

            there are probably several ways to do this, I think you can do it like:

             

            TMP:

            LOAD

            subfield(Option_Code,'_') as SubCode,

            recno() as RecNo,

            rowno() as SortOrder

            INLINE [

            Option_Code

            0500_0599_VALUE_PKG

            9078_FENDER_LIGHTS

            9188_ENGINE_BLK_HTR_110VG

            0001_0249_DESTN_COUNTRY

            ];

             

            RESULT:

            LOAD concat(SubCode,'_',SortOrder) as Option_Code resident TMP where isText(SubCode) group by RecNo;

             

            drop table TMP;

            • Re: Separate a String
              m w

              Try:

              let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

               

              mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as YourfieldName,

              • Re: Separate a String
                Liz Bauer

                Thank you for all your help!

                I was able to have a separate column within the Option Code listbox, however i'd like to make a separate listbox with just the Option Box name

                  • Re: Separate a String
                    Stefan Wühl

                    It would be best to create a new field in the script, using any of the suggested solutions (I would prefer mwoolf's one)

                     

                    Like

                     

                    let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

                     

                    LOAD

                    Option_Code,

                    mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as Option_Box,

                    ...

                    from YourTable;