Introduction/ Issue: Write the issue that you face or the issue that you want to provide a solution through this blog.
Credit Card number will be formatting based on input parameters (credit card number, credit card type).
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 credit card numbers should be format for easy identification.
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 format the credit card number based on input parameter.
CREATE FUNCTION FN_CREDTI_CARD_FORMAT(cc_number VARCHAR2,cc_type VARCHAR2) RETURN VARCHAR2 IS
format_cc_num VARCHAR2(20);
BEGIN
IF cc_type IN (‘VI’,’MC’,’DS’,’JC’,’BL’,’PA’) THEN
format_cc_num :=
SUBSTR(cc_number,1,4)||’ ‘||SUBSTR(cc_number,5,4)||’ ‘||
SUBSTR(cc_number,9,4)||’ ‘||SUBSTR(cc_number,13,4)||’ ‘||
SUBSTR(cc_number,17,4);
ELSIF cc_type IN (‘DI’,’AX’,’OP’,’CB’) THEN
format_cc_num :=
SUBSTR(cc_number,1,4)||’ ‘||SUBSTR(cc_number,5,6)||’ ‘||
SUBSTR(cc_number,11,5);
ELSIF cc_type =’FB’ THEN
format_cc_num :=
SUBSTR(cc_number,1,4)||’ ‘||SUBSTR(cc_number,5,6)||’ ‘||
SUBSTR(cc_number,11,1);
ELSIF cc_type=’SR’ THEN
format_cc_num :=
SUBSTR(cc_number,1,3)||’ ‘||SUBSTR(cc_number,4,4)||’ ‘||
SUBSTR(cc_number,8,4)||’ ‘||SUBSTR(cc_number,12,5);
ELSE
DECLARE
ln_cc_length NUMBER;
lc_format_num VARCHAR2(30);
lc_format_num1 VARCHAR2(30);
lc_remain_num VARCHAR2(30);
ln_rem_length NUMBER;
BEGIN
lc_remain_num := RTRIM(LTRIM(cc_number));
ln_cc_length := LENGTH(RTRIM(LTRIM(cc_number)));
ln_rem_length := ln_cc_length;
lc_remain_num := RTRIM(LTRIM(cc_number));
WHILE ln_rem_length > 0
LOOP
lc_format_num1 := SUBSTR(lc_remain_num,ln_rem_length-3,4);
lc_remain_num := SUBSTR(lc_remain_num,0,ln_rem_length-4);
ln_rem_length := NVL(LENGTH(lc_remain_num),0);
lc_format_num := lc_format_num1||’ ‘||lc_format_num;
END LOOP;
ln_rem_length := LENGTH(RTRIM(LTRIM(cc_number)))-LENGTH(REPLACE(lc_format_num,’ ‘,”));
IF ln_rem_length > 0 THEN
lc_format_num1 := SUBSTR(RTRIM(LTRIM(cc_number)),0,ln_rem_length);
lc_format_num := lc_format_num1||’ ‘||RTRIM(LTRIM(lc_format_num));
END IF;
format_cc_num := lc_format_num;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (‘ERROR ‘|| SQLERRM);
END ;
END IF;
RETURN(format_cc_num);
END;
Output:
Here we are passing credit card number and credit card type and get the format.
Conclusion: How did we provide an effective solution and resolve that issue/ unique solution given etc.
We can format the credit card numbers and easily read the digits.
