This User Defined Function (UDF) doesn’t require much explanation. Payment card number goes in; payment card type comes out. Since it is designed for speed, it does not validate the check digit. A subsequent post will provide a UDF to validate the check digit using the Luhn algorithm.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | /********************************************************************************************************************Function: PaymentCardTypeDescription: Decodes the type of payment card from Visa, Mastercard, AMEX, etc.Parameters: A string indicating the type of payment card, or a blank string if not identified. *********************************************************************************************************************/create or replace function PaymentCardType(cardNumber string) returns string language javascript strict as ' //Remove all spaces and dashes. Simply ignore them. NUMBER = CARDNUMBER.replace(/ /g, ""); NUMBER = NUMBER.replace(/-/g, ""); // Visa var re = new RegExp("(4[0-9]{15})"); if (NUMBER.match(re) != null) return "Visa"; // Mastercard re = new RegExp("(5[1-5][0-9]{14})"); if (NUMBER.match(re) != null) return "Mastercard"; // AMEX re = new RegExp("^3[47]"); if (NUMBER.match(re) != null) return "AMEX"; // Discover re = new RegExp("^(6011|622(12[6-9]|1[3-9][0-9]|[2-8][0-9]{2}|9[0-1][0-9]|92[0-5]|64[4-9])|65)"); if (NUMBER.match(re) != null) return "Discover"; // Diners re = new RegExp("^36"); if (NUMBER.match(re) != null) return "Diners"; // Diners - Carte Blanche re = new RegExp("^30[0-5]"); if (NUMBER.match(re) != null) return "Diners - Carte Blanche"; // JCB re = new RegExp("^35(2[89]|[3-8][0-9])"); if (NUMBER.match(re) != null) return "JCB"; // Visa Electron re = new RegExp("^(4026|417500|4508|4844|491(3|7))"); if (NUMBER.match(re) != null) return "Visa Electron"; return ""; ';-- Test the UDF:select PaymentCardType('4470653497431234'); |