La mayor\u00eda de los sellos de tarjetas de cr\u00e9dito (Visa, Master, Dinners, etc) usan el algoritmo de Luhn<\/strong>, el cual mediante un digito verificador corrobora si el resto de los n\u00fameros son correctos.
https:\/\/es.wikipedia.org\/wiki\/Algoritmo_de_Luhn<\/a><\/p>\n\n\n\n
Existen diferentes formas de implementar el algoritmo. En esta ocasi\u00f3n les comparto el script de Derek Colley<\/p>\n\n\n\n
Veamos la funci\u00f3n para validar con TSQL<\/h3>\n\n\n\nCREATE FUNCTION dbo.usp_LuhnsAlgorithm_New ( @inputString VARCHAR(20) )\nRETURNS TINYINT \nAS BEGIN \n-------------------------------------------------------------------------------\n-- Function to calculate whether a number is valid according to the 'MOD 10'\n-- check, a.k.a. Luhn's Algorithm.\n-- Author: Derek Colley, August 2014\n-- Parameters: @inputString VARCHAR(20) \n-- Outputs: TINYINT: 2 = an error occurred, validity undetermined\n-- 0 = number is not valid \n-- 1 = number is valid\n-------------------------------------------------------------------------------\n\n-- first a quick check to ensure we have at least 3 numbers in the string - \n-- you can change this to any arbitrary amount, i.e. if you are just \n-- checking credit card numbers, make it 13 digits\n\nDECLARE @result TINYINT\n\nIF @inputString NOT LIKE ('%[0-9]%[0-9]%[0-9]%') \n RETURN 2\n\n-- set up our table for algorithm calculation\n\nDECLARE @charTable TABLE ( \n Position INT NOT NULL, \n ThisChar CHAR(1) NOT NULL, \n Doubled TINYINT, \n Summed TINYINT ) \n\n-- convert the @inputString to a fixed width char datatype \n-- we can then process the string as a set with a known number of elements\n-- this avoids RBAR substringing each char to a table in a cursor\n\nSET @inputString = CAST(@inputString AS CHAR(20))\nINSERT INTO @charTable(Position, ThisChar) \n SELECT 1, SUBSTRING(@inputString, 1, 1) UNION ALL \n SELECT 2, SUBSTRING(@inputString, 2, 1) UNION ALL \n SELECT 3, SUBSTRING(@inputString, 3, 1) UNION ALL \n SELECT 4, SUBSTRING(@inputString, 4, 1) UNION ALL \n SELECT 5, SUBSTRING(@inputString, 5, 1) UNION ALL \n SELECT 6, SUBSTRING(@inputString, 6, 1) UNION ALL \n SELECT 7, SUBSTRING(@inputString, 7, 1) UNION ALL \n SELECT 8, SUBSTRING(@inputString, 8, 1) UNION ALL \n SELECT 9, SUBSTRING(@inputString, 9, 1) UNION ALL \n SELECT 10, SUBSTRING(@inputString, 10, 1) UNION ALL \n SELECT 11, SUBSTRING(@inputString, 11, 1) UNION ALL \n SELECT 12, SUBSTRING(@inputString, 12, 1) UNION ALL \n SELECT 13, SUBSTRING(@inputString, 13, 1) UNION ALL \n SELECT 14, SUBSTRING(@inputString, 14, 1) UNION ALL \n SELECT 15, SUBSTRING(@inputString, 15, 1) UNION ALL \n SELECT 16, SUBSTRING(@inputString, 16, 1) UNION ALL \n SELECT 17, SUBSTRING(@inputString, 17, 1) UNION ALL \n SELECT 18, SUBSTRING(@inputString, 18, 1) UNION ALL \n SELECT 19, SUBSTRING(@inputString, 19, 1) UNION ALL \n SELECT 20, SUBSTRING(@inputString, 20, 1)\n\n\n-- remove non-numerics inc. whitespace from the string \nDELETE FROM @charTable\nWHERE ThisChar NOT LIKE('[0-9]') \n\n\n-- unfortunately this messes up the Position indicator, \n-- so let's 'reset' this like so... \nDECLARE @tempTable TABLE ( \n NewPosition INT IDENTITY(1,1), \n OldPosition INT ) \nINSERT INTO @tempTable (OldPosition)\n SELECT Position \n FROM @charTable \n ORDER BY Position ASC \n\nUPDATE @charTable\nSET Position = t2.NewPosition \nFROM @charTable t1 \nINNER JOIN @tempTable t2 ON t1.Position = t2.OldPosition \n\n-- now for every 2nd digit from the right of the numeric, \n-- double it and store the result in the Doubled column \n\nIF ( SELECT MAX(Position) % 2 FROM @charTable ) = 0 -- evens \nBEGIN \n UPDATE @charTable\n SET Doubled = CAST(ThisChar AS TINYINT) * 2 \n WHERE Position % 2 <> 0 \nEND\nELSE BEGIN -- odds\n UPDATE @charTable \n SET Doubled = CAST(ThisChar AS TINYINT) * 2 \n WHERE Position % 2 = 0 \nEND \n\n\n-- now if the doubled digit is > 9, sum the digits, else carry forward\n-- to the Summed column. This goes for non-doubled digits too.\nUPDATE @charTable\nSET Summed = \n CASE WHEN Doubled IS NULL \n THEN CAST(ThisChar AS TINYINT) \n WHEN Doubled IS NOT NULL AND Doubled <= 9 \n THEN Doubled \n WHEN Doubled IS NOT NULL AND Doubled >= 10 \n -- sum the digits. Luckily SQL Server butchers int division...\n THEN (Doubled \/ 10) + (Doubled - 10) \n END \n\n\n-- finally, sum the Summed column and if the result % 10 = 0, it's valid \nIF ( SELECT SUM(Summed) % 10 FROM @charTable ) = 0\n SET @result = 1\nELSE \n SET @result = 0\n\nRETURN @result \nEND<\/pre>\n\n\n\nPara probar:<\/p>\n\n\n\n
SELECT '371449635398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('371449635398431') [Valid Card?] UNION ALL\nSELECT '3714 4963 5398 431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('3714 4963 5398 431') [Valid Card?] UNION ALL\nSELECT '37XX XXXX 5398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('37XX XXXX 5398431') [Valid Card?] UNION ALL\nSELECT 'This is not a valid string' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('This is not a valid string' ) [Valid Card?] UNION ALL\nSELECT '1234123412341234' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('1234123412341234') [Valid Card?]<\/pre>\n\n\n\nFuente:<\/p>\n\n\n\n
Para probar:<\/p>\n\n\n\n
SELECT '371449635398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('371449635398431') [Valid Card?] UNION ALL\nSELECT '3714 4963 5398 431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('3714 4963 5398 431') [Valid Card?] UNION ALL\nSELECT '37XX XXXX 5398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('37XX XXXX 5398431') [Valid Card?] UNION ALL\nSELECT 'This is not a valid string' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('This is not a valid string' ) [Valid Card?] UNION ALL\nSELECT '1234123412341234' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('1234123412341234') [Valid Card?]<\/pre>\n\n\n\nFuente:<\/p>\n\n\n\n
![\"Validar](\"https:\/\/i0.wp.com\/maurobernal.com.ar\/blog\/wp-content\/uploads\/2017\/11\/validar_credit_card-300x91.png?resize=300%2C91&ssl=1\")
<\/figure><\/div>\n\n\n\n