I spent a day looking around to find a graceful checksum calculation for the 16th digit in SQL. Here’s my final version that takes your source table and credit card column and returns your output_dateset.
/*
CC checksum logic
Logic based ON logic FROM these pages:
https://www.mssqltips.com/sqlservertip/3319/implementing-luhns-algorithm-in-tsql-to-validate-credit-card-numbers/
https://www.freeformatter.com/credit-card-number-generator-validator.html#:~:text=Multiply%20the%20digits%20in%20odd%20positions%20%281%2C%203%2C,to%20get%20a%20multiple%20of%2010%20%28Modulo%2010%29
This code is designed in-line so you can feed large datasets through it, rather than doing line-by-line processing.
Put your source table in the source_table CTE.
Define your source_table's credit card column to calculate the 16th number in the CTE definition of population_list for CC_to_checksum.
Output is source_table.*, CC_to_checksum char(15), checksum_number char(1), final_CC_number char(16).
*/
WITH source_table
AS (SELECT DISTINCT
LEFT(REPLACE(
REPLACE(
CAST(RAND(num * 1.4) * 100000 AS CHAR(7)) + CAST(RAND(num * .8) * 135790 AS CHAR(7))
+ CAST(RAND(num * 3.14159) * 123456 AS CHAR(7)),
'.',
''
),
' ',
''
), 15) AS example_cc_number
FROM
(
SELECT t.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM INFORMATION_SCHEMA.COLUMNS
) t
WHERE num <= 15
) gimme_cards --Make example CCs
),
population_list
AS (SELECT *,
example_cc_number AS CC_to_checksum --Define your Credit_Card_Number source column (only left 15 characters are taken into account)
FROM source_table),
output_dataset
AS (SELECT pl.*,
k AS checksum_number,
final_fomatting.CC_to_checksum + k AS final_CC_number
FROM
(
SELECT CC_to_checksum,
RIGHT(CAST(10
- (char_01 + char_02 + char_03 + char_04 + char_05 + char_06 + char_07 + char_08 + char_09
+ char_10 + char_11 + char_12 + char_13 + char_14 + char_15
) % 10 AS VARCHAR), 1) AS k
FROM
(
SELECT CC_to_checksum,
char_01 % 10 + CAST(char_01 / 10 AS INT) AS char_01,
char_02 % 10 + CAST(char_02 / 10 AS INT) AS char_02,
char_03 % 10 + CAST(char_03 / 10 AS INT) AS char_03,
char_04 % 10 + CAST(char_04 / 10 AS INT) AS char_04,
char_05 % 10 + CAST(char_05 / 10 AS INT) AS char_05,
char_06 % 10 + CAST(char_06 / 10 AS INT) AS char_06,
char_07 % 10 + CAST(char_07 / 10 AS INT) AS char_07,
char_08 % 10 + CAST(char_08 / 10 AS INT) AS char_08,
char_09 % 10 + CAST(char_09 / 10 AS INT) AS char_09,
char_10 % 10 + CAST(char_10 / 10 AS INT) AS char_10,
char_11 % 10 + CAST(char_11 / 10 AS INT) AS char_11,
char_12 % 10 + CAST(char_12 / 10 AS INT) AS char_12,
char_13 % 10 + CAST(char_13 / 10 AS INT) AS char_13,
char_14 % 10 + CAST(char_14 / 10 AS INT) AS char_14,
char_15 % 10 + CAST(char_15 / 10 AS INT) AS char_15
FROM
(
SELECT CC_to_checksum,
char_01 * 2 AS char_01,
char_02,
char_03 * 2 AS char_03,
char_04,
char_05 * 2 AS char_05,
char_06,
char_07 * 2 AS char_07,
char_08,
char_09 * 2 AS char_09,
char_10,
char_11 * 2 AS char_11,
char_12,
char_13 * 2 AS char_13,
char_14,
char_15 * 2 AS char_15
FROM
(
SELECT CC_to_checksum,
SUBSTRING(CC_to_checksum, 01, 1) AS char_01,
SUBSTRING(CC_to_checksum, 02, 1) AS char_02,
SUBSTRING(CC_to_checksum, 03, 1) AS char_03,
SUBSTRING(CC_to_checksum, 04, 1) AS char_04,
SUBSTRING(CC_to_checksum, 05, 1) AS char_05,
SUBSTRING(CC_to_checksum, 06, 1) AS char_06,
SUBSTRING(CC_to_checksum, 07, 1) AS char_07,
SUBSTRING(CC_to_checksum, 08, 1) AS char_08,
SUBSTRING(CC_to_checksum, 09, 1) AS char_09,
SUBSTRING(CC_to_checksum, 10, 1) AS char_10,
SUBSTRING(CC_to_checksum, 11, 1) AS char_11,
SUBSTRING(CC_to_checksum, 12, 1) AS char_12,
SUBSTRING(CC_to_checksum, 13, 1) AS char_13,
SUBSTRING(CC_to_checksum, 14, 1) AS char_14,
SUBSTRING(CC_to_checksum, 15, 1) AS char_15
FROM population_list
) parse_characters
) pre_sum_conversion
) roll_it_up
) final_fomatting
INNER JOIN population_list pl
ON pl.CC_to_checksum = final_fomatting.CC_to_checksum)
SELECT *
FROM output_dataset;
The idea behind this is that by doing in-line processing, we/you/I can take advantage of the SQL server’s stream processing that it is so good at, rather than creating an in-line cursor.
I have populated the source_table with garbage numbers to show that it works. Please replace that full CTE with your actual view/table.
PLSQL note: You’ll need to adjust at least the % to mod(m,n) and SUBSTRING() to SUBSTR().
deltainnovativelabs 