{"id":8749,"date":"2016-02-16T15:18:49","date_gmt":"2016-02-16T15:18:49","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-luhn-algorithm-in-sql\/"},"modified":"2021-09-29T16:21:22","modified_gmt":"2021-09-29T16:21:22","slug":"the-luhn-algorithm-in-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-luhn-algorithm-in-sql\/","title":{"rendered":"The Luhn Algorithm in SQL"},"content":{"rendered":"<p>The Luhn test is used by most credit card companies to check the basic validity of a credit card number. It is not an anti-fraud measure but a quick check on data corruption. It still allows any digits that are odd or even to be switched in the sequence. Most credit cards are compatible with Luhn algorithm.<\/p>\n<p>It is often applied to SSNs, company organization numbers, and OCR numbers for internet payments. The algorithm is simple.<\/p>\n<ul>\n<li>Take out the spaces from the string containing the credit card numbers<\/li>\n<li>Reverse the string containing the credit card numbers.<\/li>\n<li>Sum every digit whose order in the sequence is an odd number (1,3,5,7 \u2026) to create a partial sum s1<\/li>\n<li>Multiply each even digit by two, and then sum the digits of the number if the answer is greater than nine. (e,g if digit is 8 then 8*2=16, then add the resulting digits: 1+6=7).<\/li>\n<li>Sum the partial sums of the even digits to form s2<\/li>\n<\/ul>\n<p>if s1 + s2 ends in zero then the original number is in the form of a valid credit card number as verified by the Luhn test.<\/p>\n<p>There are many ways of doing it in SQL. (and Rosetta Code is a good place to view solutions in various other languages). I believe that Peter Larsson holds the record for the fastest calculation of the Luhn test for a sixteen-digit credit card, with this code. As it stands, it isn\u2019t a general solution, but it can be modified for different lengths of bank card.<\/p>\n<pre class=\"lang:tsql theme:ssms2012 \" title=\"The Luhn Algorithm in SQL\">CREATE FUNCTION dbo.fnIs16digitValidCard\r\n(\r\n@Card CHAR(16)\r\n)\r\nRETURNS TINYINT\r\nAS\r\nBEGIN\r\n        RETURN  CASE\r\n                WHEN @Card LIKE '%[^0-9]%' THEN 0\r\n                WHEN @Card IS NULL THEN 0\r\n                WHEN    (\r\n                        + 2 * CAST(SUBSTRING(@Card, 1, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 1, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 2, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 3, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 3, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 4, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 5, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 5, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 6, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 7, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 7, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 8, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 9, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 9, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 10, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 11, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 11, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 12, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 13, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 13, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 14, 1) AS TINYINT)\r\n                        + 2 * CAST(SUBSTRING(@Card, 15, 1) AS TINYINT) \/ 10\r\n                        + 2 * CAST(SUBSTRING(@Card, 15, 1) AS TINYINT) % 10\r\n                        + CAST(SUBSTRING(@Card, 16, 1) AS TINYINT)\r\n                ) % 10 = 0 THEN 1\r\n                ELSE 0\r\n        END\r\nEND\r\n<\/pre>\n<p>Credit card numbers in general can have anywhere between eleven and twenty digits, depending on the provider. You can, of course, make Peter\u2019s code more generic, though I suspect he\u2019s come up with something even more blindingly fast.<\/p>\n<pre class=\"lang:tsql theme:ssms2012 \" title=\"The Luhn Algorithm in SQL\"> \r\nCREATE FUNCTION dbo.fnIsValidCard\r\n(\r\n@TheCard VARCHAR(20)\r\n)\r\nRETURNS TINYINT\r\nAS\r\nBEGIN\r\n       RETURN (SELECT CASE\r\n              WHEN Card LIKE '%[^0-9]%' THEN 0\r\n              WHEN Card IS NULL THEN 0\r\n              WHEN   (\r\n                     + 2 * cast(substring(Card, 1, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 1, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 2, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 3, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 3, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 4, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 5, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 5, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 6, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 7, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 7, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 8, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 9, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 9, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 10, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 11, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 11, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 12, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 13, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 13, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 14, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 15, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 15, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 16, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 17, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 17, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 18, 1) AS TINYINT)\r\n                     + 2 * cast(substring(Card, 19, 1) AS TINYINT) \/ 10\r\n                     + 2 * cast(substring(Card, 19, 1) AS TINYINT) % 10\r\n                     + cast(substring(Card, 20, 1) AS TINYINT)\r\n              ) % 10 = 0 THEN 1\r\n              ELSE 0\r\n       END FROM (\r\n             VALUES (\r\n                  convert(CHAR(20),\r\n                  CASE WHEN len(@TheCard)%2=1 THEN '0' ELSE '' END\r\n                     +@TheCard+'00000000'\r\n                     )\r\n                )\r\n           )f(card))\r\nEND\r\n<\/pre>\n<p>This works because 0 digits do not affect the test, and you can merely assume that all strings are 20 characters long and zero-pad strings. If strings are odd in length (11,13,15 etc) then you can prepend a zero without affecting the checksum.<\/p>\n<p>OK. So here is a pretty good solution that one can benchmark your own solution against. You would, of course, need to make sure that your solution actually can distinguish bogus from real cards. Here is a temporary table of various test cards and some bogus cards made by changing a few digits. Your solution must be able to correctly find the bogus cards. We can then check whether you have mistaken a bogus card for a good one and vice versa.<\/p>\n<pre class=\"lang:tsql theme:ssms2012 \" title=\"The Luhn Algorithm in SQL\"> \r\nIF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE name LIKE '#testdata%')\r\n       DROP TABLE #testData\r\nCREATE TABLE #testData (Provider VARCHAR(30),Number VARCHAR(20))\r\nINSERT INTO #TestData(Provider,Number)\r\n SELECT provider, number FROM (VALUES\r\n       ('Airplus','122000000000003'),\r\n       ('bogus','122002004005003'),\r\n       ('American Express','34343434343434'),\r\n       ('bogus','34343431343434'),\r\n       ('American Express','378282246310005'),\r\n       ('bogus','178282246310005'),\r\n       ('American Express','371449635398431'),\r\n       ('bogus','371494635398431'),\r\n       ('American Express Corporate','378734493671000'),\r\n       ('bogus','378734493671001'),\r\n       ('Australian BankCard','5610591081018250'),\r\n       ('bogus','561059108101825'),\r\n       ('Dankort (PBS)','5019717010103742'),\r\n       ('bogus','5019710710103742'),\r\n       ('Diners Club','30569309025904'),\r\n       ('bogus','36569309025904'),\r\n       ('Diners Club','38520000023237'),\r\n       ('bogus','38627080923237'),\r\n       ('Diners Club','36700102000000'),\r\n       ('bogus','36701002000001'),\r\n       ('Diners Club','36148900647913'),\r\n       ('bogus','36148910640913'),\r\n       ('Discover card','6011000400000000'),\r\n       ('Discover card','6011111111111117'),\r\n       ('Discover card','6011000990139424'),\r\n       ('bogus','6011000980139424'),\r\n       ('JCB','3530111333300000'),\r\n       ('JCB','3566002020360505'),\r\n       ('JCB','3528000700000000'),\r\n       ('Bogus','3528000000000000'),\r\n       ('Laser','630495060000000000'),\r\n       ('Laser','630490017740292441'),\r\n       ('Maestro','6759649826438453'),\r\n       ('Maestro','6799990100000000019'),\r\n       ('Mastercard','5555555555554444'),\r\n       ('Mastercard','5454545454545454'),\r\n       ('MasterCard','5105105105105100'),\r\n       ('Switch\/Solo (Paymentech)','6331101999990016'),\r\n       ('Visa','4111111111111111'),\r\n       ('Visa','4012888888881881'),\r\n       ('Visa','4222222222222'),\r\n       ('Visa','4444333322221111'),\r\n       ('Visa','4911830000000'),\r\n       ('Visa','4917610000000000'),\r\n       ('Visa Debit','4462030000000000'),\r\n       ('Visa Debit','4917610000000000003'),\r\n       ('Visa Electron (UK only)','4917300800000000'),\r\n       ('Visa Purchasing','4484070000000000')\r\n       )CreditCards(Provider,Number)\r\n \r\nGO\r\nDECLARE @Results TABLE (Provider VARCHAR(30),Number VARCHAR(20), conclusion CHAR(4))\r\nINSERT INTO @results\r\n  SELECT provider, number,\r\n    CASE WHEN dbo.fnIsValidCard(number)=1 THEN 'good' ELSE 'bad' END\r\nFROM #testdata\r\nIF EXISTS (SELECT * FROM @results WHERE provider='bogus' AND conclusion='good')\r\n       RAISERROR ('false positive result in test cases',16,1)\r\nIF EXISTS (SELECT * FROM @results WHERE provider&lt;&gt;'bogus' AND conclusion='bad')\r\n       RAISERROR ('false negative result in test cases',16,1)\r\n \r\n \r\n \r\ngo\r\nDECLARE @Results TABLE (Provider VARCHAR(30),Number VARCHAR(20), conclusion CHAR(4))\r\nINSERT INTO @results\r\nSELECT\r\n  Provider,number,\r\n  CASE WHEN (sum(CASE WHEN seq%2=0 THEN \/*it is odd-numbered (from the end!)*\/\r\n        CASE WHEN digit &gt; 4 THEN (digit * 2) -9 ELSE (digit * 2) END\r\n         ELSE\/*even-numbered *\/digit END)%10=0) THEN 'good' ELSE 'bad' END AS conclusion\r\n\u2013into #result\r\nFROM (\r\n  SELECT\r\n    provider, digits, seq, number,\r\n       convert(INT,substring(number, digits+1-seq, 1)) AS digit, UniqueRow \r\n  FROM \u2013a number table\r\n       (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),\r\n               (11),(12),(13),(14),(15),(16),(17),(18),(19),(20)\r\n       )NumberTable(seq)\r\n  INNER JOIN\r\n   (SELECT Provider, number, len(number), row_number() OVER (ORDER BY (SELECT 1))\r\n       FROM\r\n    #TestData\r\n  )processedNumber(provider,number,digits,UniqueRow)\r\nON  seq&lt;=digits)g(provider, digits,seq, Number,Digit, UniqueRow)\r\nGROUP BY uniqueRow,Number,provider\r\n \r\nIF EXISTS (SELECT * FROM @results WHERE provider='bogus' AND conclusion='good')\r\n       RAISERROR ('false positive result in test cases',16,1)\r\nIF EXISTS (SELECT * FROM @results WHERE provider&lt;&gt;'bogus' AND conclusion='bad')\r\n       RAISERROR ('false negative result in test cases',16,1)\r\n<\/pre>\n<p>So there you have it: a couple of SQL implementations of the algorithm. A spoiler alert is that they run at roughly the same speed but Peter\u2019s version seems to have the edge in my timings. Can you think of a quicker, or more elegant, way of doing it?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Luhn test is used by most credit card companies to check the basic validity of a credit card number. It is not an anti-fraud measure but a quick check on data corruption. It still allows any digits that are odd or even to be switched in the sequence. Most credit cards are compatible with Luhn algorithm. It is often applied to SSNs, company organization numbers, and OCR numbers for internet payments. How do you handle them in SQL?&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[],"coauthors":[6813],"class_list":["post-8749","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8749"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8749\/revisions"}],"predecessor-version":[{"id":80259,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8749\/revisions\/80259"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8749"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}