1 Reply Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Instr() function

    michaeljul _

      I have a need to use this function to look for a specific character string, which is defined as always starting with two letters and be followed by a dash and then 6 numbers. Is there a way to do this? thought Instr("??-######",IDNO) would work, but it doesnt. Any suggestions?

        • Instr() function
          Grant Perkins

          Michael,

           

          With some reservations about the uniqueness of a "-" in a data field - so this may not always be successful but ...

           

          isalpha(left(substr(,Instr("-",[FIELD NAME])-2,9),2))

           

          Should return a 1 if the first character is ALPHA, a 0 if it is NOT.

           

          isalpha(right(substr(,Instr("-",[FIELD NAME])-2,9),2))

           

          Should pick the numeric  part of the string and return a 0 to indicate it is not alpha.

           

          ISALPHA has some constraints and you may need to apply the check to each character in turn for your purposes but it would allow you to check whether a string identified based on the presence of a "-" somewhere in a field appears to have the required structure.

           

          It will only find the first "-" in the field (unless you add to the process and make it more exacting).

           

           

          Components of the formula;

           

          INSTR seeks out a "-" and returns its position.

           

          SUBSTR then selects all the characters from 2 before the number returned by INSTR for a total of 9.

           

          LEFT picks the first 2 characters of the 9 character string selected.

           

          ISALPHA checks the first character of the 2 character string and report whether it is alpha or not.

           

          In the second formula RIGHT in effect picks the last character of the 9 character string and should return 0 since we expect a numeric character.

           

          It may be necessary to check all characters for the correct types in which case the same ideas but with altered parameters should work.

           

          I hope this is usuful in some way.

           

          As far As I remember there are no direct pattern matching options available.

           

          Grant

           

            Originally posted by michaeljul:

          I have a need to use this function to look for a specific character string, which is defined as always starting with two letters and be followed by a dash and then 6 numbers. Is there a way to do this? thought Instr("??-######",IDNO) would work, but it doesnt. Any suggestions? /b[/quote]