5 Replies Latest reply: May 15, 2014 9:59 AM by bickle _ RSS

    How to disable automatic trim of character fields in v6?

    bickle _

      If I extract a character field and the last characters are spaces, when I run a calculated field on that data, the trailing spaces are being automatically omitted.

       

      For example, if I pull a 6 character field and the contents of the field are " data " (note the preceding and trailing spaces), a calculated field based upon that record will see only " data". If I used left() to extract 1 character, I will get " ", whereas right() would disregard the trailing space & will return "a".

       

      Can this be disabled, and how? This behavior was not present in earlier versions, and not creates inconsistencies in the behavior of the left() and right() functions.

       

      Thanks,

        • How to disable automatic trim of character fields in v6?
          Mike Urbonas

          I was not aware of this difference in the RIGHT() function in V5 versus V6 or V7.  Anecdotally, I recall as a Monarch user that the help for Monarch V1 mentioned that Monarch automatically removed trailing spaces from a character field.  I don't have Monarch V5 on my PCs now, just V6 and V7, so I can't test this, at least not at this moment.

           

          However, here is a quick fix that should guarantee your RIGHT() function will pick up trailing spaces that you are looking for.  Let's stick with your example of a character field with a width of 6 (we'll name this field: Example).  Try this formula, which should "pad" the data with the correct number of extra spaces, if any:

           

          RIGHT(Example+SPACE(6-LEN(Example)),1)

           

          Using the above formula on a data field containing " data " should yield a space.  Of course, if a character field is width is not 6, be sure to adjust the formula accordingly.

           

          Hope this helps,

           

          Mike

          • How to disable automatic trim of character fields in v6?
            Grant Perkins

            I have just checked V5, V6 and V7 and they all appear to do the same thing and drop any trailing spaces.

             

            Which is interesting because both the V5 and  V7 Help pages for the LEN() function offer specific guidance about how to remove trailing spaces if required.

             

            bickle,

             

            which original version are you moving up from?

             

            Are we all getting a bit confused here? (Well, I know I am but then there was a question last year -- from Tom Whiteside I think -- about trailing spaces in multi-row fields and I recall that that one got a bit involved at the time!)

             

            Err. Over to you Mike!

             

            Grant

             

             

            Originally posted by Mike Urbonas:

            I was not aware of this difference in the RIGHT() function in V5 versus V6 or V7.  Anecdotally, I recall as a Monarch user that the help for Monarch V1 mentioned that Monarch automatically removed trailing spaces from a character field.  I don't have Monarch V5 on my PCs now, just V6 and V7, so I can't test this, at least not at this moment.

             

            /b[/quote]

            • How to disable automatic trim of character fields in v6?
              Tom Whiteside

              Thanks for the fond memory of last year's posting [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000000"]labyrinth[/url], Grant!  However, as that dealt with LSplit and RTrim, and their problems with the "untrimmable space," i.e., the combination CrLf of (Chr(13) and Chr(10)), it probably won't have too much relevance to bickle's Left() and Right() functions.  However, Nick Osdale-Popa's elegant solution to the problem can be profitably read by everyone (the third posting).

               

              [size="1"][ May 19, 2006, 12:16 PM: Message edited by: Todd Niemi ][/size]

              • How to disable automatic trim of character fields in v6?
                Grant Perkins

                True enough Tom, but in some ways the consideration of how to deal with a space/no space requirement, by whatever method chosen in the development, is worthy of broad understanding wherever possible. Mainly because the knowledge may lead to less personal confusion! (My preferred excuse.)Or, perhaps more likely, enough of a recollection of the issues to be able to figure out a solution to the problem with which one can be satisfied?

                 

                It is interesting to observe that the anomally seems to have been around for quite some time without it being identified. (UNLESS of course I have some funny settings on my machines that that I know nothing about but which are producing this undocumented behaviour.)

                 

                Presumably the problem has not been causing much grief in that time, which in itself is interesting. Either that or I have something very strange going on.

                 

                Whilst this does not really help Bickle - though I'm not sure if the Left() and Right() functions are the only calculations bickle is concerned with and others functions may have their own foibles to contend with - a broader consideration of text field handling requirements vs what the system does is no bad thing. At least making the link allows people to assess what sort of problem (if any) they may have.

                 

                So personally I see some benefit in making the link although I agree that it is not directly beneficial to bickle's immediate needs.

                 

                But that is just my opinion.

                 

                I'm looking forward to the next instalment on this one.

                 

                Grant

                 

                 

                  Originally posted by Tom Whiteside:

                Thanks for the fond memory of last year's posting [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000000"]labyrinth[/url], Grant!  However, as that dealt with LSplit and RTrim, and their problems with the "untrimmable space," i.e., the combination CrLf of (Chr(13) and Chr(10)), it probably won't have too much relevance to bickle's Left() and Right() functions.  /b[/quote]

                 

                [size="1"][ May 19, 2006, 12:16 PM: Message edited by: Todd Niemi ][/size]

                • How to disable automatic trim of character fields in v6?
                  bickle _

                  Our organization has recently upgraded from v3 (ancient!) to v6.

                   

                  I can understand the benefit of automatically trimming the trailing spaces (especially for newer users), but since there are simple functions within Monarch to manually acheive the same ends, it would be handy if the automatic trim was an option that could be disabled. Perhaps in the "Report Options" under "Input Options"? Or in the options for the individual fields?

                   

                  This turned up after some users reported that their previous models were reporting different results after the upgrade. I've recommended that they change their models to use the Substr() function instead. Exporting results as a fixed-length text file has been used another way to work around the issue. I'll hold on to Mike's suggestion as well (thanks!) - it looks like a very good alternative.

                   

                  Because of the great flexibility of Monarch, there are a number of solutions to work around the behavior, but it is an odd change.