Introduction/ Issue:  Write the issue that you face or the issue that you want to provide a solution through this blog.

Mobile number should be digits

 

 

Why we need to do / Cause of the issue: Write the details about the issue – how does it occur and what is the impact of it.

For every mobile numbers should be only digits. We need to validate the characters.

 

 

How do we solve: Write the Query or the steps to solve the issue with your real-time example or scenarios. Note: do not mention any customer name / VPN details.

We can create one function in database and validate the mobile number based on in/out parameter.

CREATE OR REPLACE FUNCTION FN_MOBILE_NUMBER_FORMAT (TEL_IN IN OUT VARCHAR2)

   RETURN VARCHAR2

IS

 

   C_NUM_CHK   NUMBER;

   C_TEL_LEN   NUMBER;

BEGIN

   C_TEL_LEN := NVL (LENGTH (LTRIM (RTRIM (TEL_IN))), 0);

 

   IF     c_tel_len = 13

      AND (    SUBSTR (TEL_IN, 1, 1) = ‘(‘

           AND SUBSTR (TEL_IN, 5, 1) = ‘)’

           AND SUBSTR (TEL_IN, 9, 1) = ‘-‘)

   THEN

      tel_in :=

            SUBSTR (TEL_IN, 2, 3)

         || SUBSTR (TEL_IN, 6, 3)

         || SUBSTR (TEL_IN, 10, 4);

      c_tel_len := 10;

   ELSIF c_tel_len != 10

   THEN

      BEGIN

         c_num_chk := TO_NUMBER (tel_in);

         RETURN TEL_IN;

      EXCEPTION

         WHEN VALUE_ERROR

         THEN

            dbms_output.put_line (‘Number should be in format nnnnnnnnnnnnnn… Only’);

      END;

   END IF;

 

   IF C_TEL_LEN > 20

   THEN

      IF (   SUBSTR (TEL_IN, 1, 1) <> ‘(‘

          OR SUBSTR (TEL_IN, 5, 1) <> ‘)’

          OR SUBSTR (TEL_IN, 9, 1) <> ‘-‘)

      THEN

         dbms_output.put_line  (‘Phone number should be in format (nnn)nnn-nnnnnnnnnnnnnn’);

      ELSE

         BEGIN

            C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 2, 3));

            C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 6, 3));

            C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 10, C_TEL_LEN – 9));

         EXCEPTION

            WHEN VALUE_ERROR

            THEN

               dbms_output.put_line(‘Number should be in format (nnn)nnn-nnnnnnnnnnnnnn Only…’);

         END;

      END IF;

 

      RETURN TEL_IN;

   ELSE

      IF C_TEL_LEN = 10

      THEN

         BEGIN

            C_NUM_CHK := TO_NUMBER (TEL_IN);

         EXCEPTION

            WHEN VALUE_ERROR

            THEN

               dbms_output.put_line(‘Number should be in format nnnnnnnnnnnnnnnnnnnn Only…’);

         END;

 

         RETURN (   ‘(‘

                 || SUBSTR (TEL_IN, 1, 3)

                 || ‘)’

                 || SUBSTR (TEL_IN, 4, 3)

                 || ‘-‘

                 || SUBSTR (TEL_IN, 7, C_TEL_LEN – 6));

      ELSE

         IF SUBSTR (TEL_IN, 1, 1) = ‘(‘

         THEN

            IF C_TEL_LEN < 13

            THEN

               dbms_output.put_line (‘Phone number should be in format (nnn)nnn-nnnnnnnnnnnnnn’);

            ELSIF (   SUBSTR (TEL_IN, 1, 1) <> ‘(‘

                   OR SUBSTR (TEL_IN, 5, 1) <> ‘)’

                   OR SUBSTR (TEL_IN, 9, 1) <> ‘-‘)

            THEN

               dbms_output.put_line(‘Phone number should be in format (nnn)nnn-nnnnnnnnnnnnnn’);

            ELSE

               BEGIN

                  C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 2, 3));

                  C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 6, 3));

                  C_NUM_CHK := TO_NUMBER (SUBSTR (TEL_IN, 10, C_TEL_LEN – 9));

               EXCEPTION

                  WHEN VALUE_ERROR

                  THEN

                     dbms_output.put_line(‘Number should be in format (nnn)nnn-nnnnnnnnnnnnnn Only…’);

               END;

            END IF;

 

            RETURN TEL_IN;

         ELSE

            BEGIN

               C_NUM_CHK := TO_NUMBER (TEL_IN);

            EXCEPTION

               WHEN VALUE_ERROR

               THEN

                  dbms_output.put_line(‘Number should be in format nnnnnnnnnnnnnnnnnnnn Only…’);

            END;

 

            RETURN (   ‘(‘

                    || SUBSTR (TEL_IN, 1, 3)

                    || ‘)’

                    || SUBSTR (TEL_IN, 4, 3)

                    || ‘-‘

                    || SUBSTR (TEL_IN, 7, C_TEL_LEN – 6));

         END IF;

      END IF;

   END IF;

END;

/

 

Output:

Here we are passing the mobile number and get the format.

 

Here we are passing the mobile number with special character and character and getting the error.

Conclusion: How did we provide an effective solution and resolve that issue/ unique solution given etc.

We can validate the mobile numbers only in digits and avoid the characters and special characters.

 

Recent Posts

Start typing and press Enter to search