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.
