5 Replies Latest reply: May 15, 2014 10:10 AM by elginreigner _ RSS

    Problem with Chr function

    Glen Nickerson

      I'm trying to create a calculated numeric field with a value of 1 or zero based on the following:   if(partstr=chr(042),1,0)    as I'm looking for an * in a string field defined as one character in length.  After execution, the value of the new field, regardless of the character contained in the string, is always 1. Any ideas on what I'm doing wrong? The business problem is to determine if any of the keyboards special characters are contained in a data entry field.

       

      Nick

       

      /SIZE

        • Problem with Chr function
          Steve Caiels

          [FONT=Calibri]Hi Nick,[/FONT][/SIZE]

           

          [FONT=Calibri]You can use the strip() function to remove undesired characters.[/FONT][/SIZE]

           

          [FONT=Calibri]Using an IF() function to test the length of the original against the length of the stripped version will allow you to test rather than clean the fields.[/FONT][/SIZE]

           

          If(len(data)<> Len(strip(data,"!£$%^&*_" + chr(34) + chr(40) + chr(41))),[/FONT]

          1,[/FONT]

          0)[/FONT]

           

          [SIZE=3]As you can see, the asterisk is no problem, just include it as a character to strip.  Quotes and brackets cause a problem as they upset the syntax, so use chr(34), chr(40), 41 etc /SIZE[/FONT][URL="http://www.asciitable.com/"][FONT=Courier New][COLOR=#0000ff]http://www.asciitable.com/[/COLOR][/FONT][/URL]

           

          [FONT=Calibri]Regards,[/FONT][/SIZE]

          [FONT=Calibri]Steve.[/FONT][/SIZE]

            • Problem with Chr function
              Glen Nickerson

              Steve,

               

              While I'm still confused as to why the documented use of the Chr function did not work, your work around produced the correct results so thank you very much.

               

              Nick

                • Problem with Chr function
                  elginreigner _

                  Glen

                   

                  I have never used the CHR function in Monarch, I normally check for the character itself, but i understand what you're after. I have tested this as well. I created a file with some test data, 3 rows, '*','%', 'TEST'. I then created a field with the created value of chr(042). I tested both fields against chr(042) and all rows returned a 1, in my setup this is stating that the values matched.

                   

                  I then went with another Idea that Monarch is not seeing the '0' in front of the 42. So I tested against chr(42), again, they all returned a match which should not be true.

                   

                  Either I'm missing something, or this is a bug.

                    • Problem with Chr function
                      Data Kruncher

                      I suspect that this unexpected result or behaviour of Chr() is not so much a bug as it is a misinterpretation of the syntax somewhat.

                       

                      As described in the original post, partstr=chr(042) always returns TRUE. This is because Monarch is interpreting the test as partstr LIKE chr(042), or literally, partstr LIKE "*". Since everything matches a wildcard, this will always return true.

                       

                      A better syntax for this problem would be Asc(partstr)=42, which tests the specific ASCII value of the single character in partstr. In which case, for example, Asc("L")=42 returns FALSE, but Asc("*")=42 returns TRUE.

                       

                      HTH.