7 Replies Latest reply: Jun 13, 2014 9:58 AM by jwellsnh RSS

    Trouble with a postal code

    jerry44

      Hi everyone. I'm in trouble with postal code. The problem is that the mask of code is 00-000 and in base i've field number(5). The result I desire is to have interface human friendly to make user unable put code with mask for example 0-0000 or 000-0 or 000-00, just 00-000. I thought about two boxes one for 00 and second for 000. Second functionality that relates to the problem is presentation, if the solution is based on two fields/columns there probably will be needed concatenation. But in my opinion atomizing code that there would be 2 collumns [00] and [000] is probably bad solution. The target is to show on repports properely [00-000] and create interface in form to fill it with cone in the [00-000] format, display in repports in format [00-000] and of course save to the base and load. If somebody has idea how to resolve the problem, please write it down

        • 1. Re: Trouble with a postal code
          TexasApexDeveloper

          How about a formatted input control?? - Item Plugin - Text Field with Masked Input

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: Trouble with a postal code
            jerry44

            I'm searching for solution that will work without instalation anything to APEX.

            • 3. Re: Trouble with a postal code
              jwellsnh

              Hi user8851646,

               

              How about:

              1. Validate that you have 5 digits only when you capture the data?
              SELECT 1
                FROM DUAL
               WHERE REGEXP_LIKE ( :postal_code, '^[[:digit:]]{5}$', 'c')
              

               

              Then when ever you present the data do something like:

              SELECT SUBSTR ( :postal_code, 1, 2) || '-' || SUBSTR ( :postal_code, 3, 3)
                FROM DUAL
              

               

              Jeff

              • 4. Re: Trouble with a postal code
                jerry44

                I do not understand this line:

                 

                REGEXP_LIKE ( :postal_code, '^[[:digit:]]{5}$', 'c'


                The problem that remains is saving data from form, should be validated at trigger level? (if in if clauses checking if place of hyphen is correct and after that remove it and add while presented in forms using aqdequate functions in repport sql code as if in Jeff's second code block?)

                • 5. Re: Trouble with a postal code
                  jwellsnh

                  Hi user8851646, do you have a name?

                   

                  That line of code contains a regular expression, regular expression looks for patterns within a string.  This particular expression one is looking for 5 digits or numbers only.

                   

                  Try the code, enter 4 digits or numbers, the result should not be 1.  Enter 6 digits the result should not be 1.  Only when you enter 5 digits are entered into the bind variable, postal_code, should the query return one.

                   

                  You might wonder, what do I do with this?  Use the regular expression to validate the entry of the your postal code so as to insure that it is always in the format you specified.

                   

                  Jeff

                  • 6. Re: Trouble with a postal code
                    jerry44

                    Hi user8851646, do you have a name?

                     

                    Jerry, I've changed it in profile settngs but still nothing...

                     

                    Try the code, enter 4 digits or numbers, the result should not be 1.  Enter 6 digits the result should not be 1.  Only when you enter 5 digits are entered into the bind variable, postal_code, should the query return one.

                     

                    I'll test that but I cannot do this now.

                     

                    You might wonder, what do I do with this?  Use the regular expression to validate the entry of the your postal code so as to insure that it is always in the format you specified.

                     

                    I wonder how to to keep that in database collumn, currently as I've written before I"ve creatd collumn postal_code NUMBER(5). So i think that data from the field can be validated before saving if format in field is propper, if yes than save without hyphen in other case raise error for example by trigger. And if the data will be loaded in field of repport or form, should be selected af if you've written in second block. Is it proper way of thinking?

                    • 7. Re: Trouble with a postal code
                      jwellsnh

                      Hi Jerry,

                       

                      Your profile will eventually change, just give it some time; maybe some humanoid is involved in releasing the update.

                       

                      Yes, the idea is to validate the data prior to writing it to the database but a trigger action is not needed.  The regular expression will only return true if there are 5 digits; anything other value or string that is not 5 digits will return false.

                       

                      As you can guess; regular expressions can become quite complex; that is why there is a ton written about it.  Google the term and go at it.

                       

                      I will include a link tomorrow with my favorite tool for constructing them.

                       

                      Here is the link to the tool I use:

                      Laboratorio Espressioni Regolari per Oracle - passo 1 di 5

                       

                      Godere Jeff

                       

                      Message was edited by: jwellsnh