4 Replies Latest reply on May 15, 2013 12:57 PM by Alex Kerin

    How to remove numbers at the end of the string

    siddhartha.siddhartha

      Hi

       

      I have this dimension which has values like

       

      abcd

      edh11

      jhdhjk543

      as221

       

      I just want to retrieve the alphabetic values from it which should be

       

      abcd

      edh

      jhdhjk

      as

       

      Please help

       

      Thanks

        • 1. Re: How to remove numbers at the end of the string
          Alex Kerin

          I would guess you are going to have to use a nested if

           

          left([Values],

          if not isnull(int(mid([Values],1,1))) then 1

          elseif not isnull(int(mid([Values],2,1))) then 2

          elseif not isnull(int(mid([Values],3,1))) then 3

          elseif not isnull(int(mid([Values],4,1))) then 4

          elseif not isnull(int(mid([Values],5,1))) then 5

          elseif not isnull(int(mid([Values],6,1))) then 6

          elseif not isnull(int(mid([Values],7,1))) then 7

          elseif not isnull(int(mid([Values],8,1))) then 8

          elseif not isnull(int(mid([Values],9,1))) then 9

          elseif not isnull(int(mid([Values],10,1))) then 10

          else len([Values])+1

          end

          -1)

           

          works, but I actually had to extract the data as the query was too complex for JET.

          1 of 1 people found this helpful
          • 2. Re: How to remove numbers at the end of the string
            siddhartha.siddhartha

            Hi Alex

             

            This solution is somewhat close, but actually if the length of the string keeps on changing then i have to add more elseif statements in the loop.

            I just want to avoid that, i need something which is a one time calculated field which is not dependant on the length of the string.

             

            Thanks

            Sid

            • 3. Re: How to remove numbers at the end of the string
              Alex Kerin

              Presumably you have a max string length - that's how far you need to go.

               

              As we do not have access to real looping, I don't think you have any other choice.

               

              EDIT: There is one other choice actually - you could nest a bunch of Replace([Values],"1","") within each other - this is length independent, but you would need to check for every number

              • 4. Re: How to remove numbers at the end of the string
                Alex Kerin

                Like this:

                 

                Replace(Replace(Replace([Values],"3",""),"2",""),"1","") all the way from 0 to 9

                 

                EDIT: here:

                 

                Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Values],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

                1 of 1 people found this helpful