Validar Tarjeta de crédito
Verificar y validar el número de una Tarjeta de Crédito o Débito
La mayoría de los sellos de tarjetas de crédito (Visa, Master, Dinners, etc) usan el algoritmo de Luhn, el cual mediante un digito verificador corrobora si el resto de los números son correctos.
https://es.wikipedia.org/wiki/Algoritmo_de_Luhn
Existen diferentes formas de implementar el algoritmo. En esta ocasión les comparto el script de Derek Colley
Veamos la función para validar con TSQL
CREATE FUNCTION dbo.usp_LuhnsAlgorithm_New ( @inputString VARCHAR(20) )
RETURNS TINYINT
AS BEGIN
-------------------------------------------------------------------------------
-- Function to calculate whether a number is valid according to the 'MOD 10'
-- check, a.k.a. Luhn's Algorithm.
-- Author: Derek Colley, August 2014
-- Parameters: @inputString VARCHAR(20)
-- Outputs: TINYINT: 2 = an error occurred, validity undetermined
-- 0 = number is not valid
-- 1 = number is valid
-------------------------------------------------------------------------------
-- first a quick check to ensure we have at least 3 numbers in the string -
-- you can change this to any arbitrary amount, i.e. if you are just
-- checking credit card numbers, make it 13 digits
DECLARE @result TINYINT
IF @inputString NOT LIKE ('%[0-9]%[0-9]%[0-9]%')
RETURN 2
-- set up our table for algorithm calculation
DECLARE @charTable TABLE (
Position INT NOT NULL,
ThisChar CHAR(1) NOT NULL,
Doubled TINYINT,
Summed TINYINT )
-- convert the @inputString to a fixed width char datatype
-- we can then process the string as a set with a known number of elements
-- this avoids RBAR substringing each char to a table in a cursor
SET @inputString = CAST(@inputString AS CHAR(20))
INSERT INTO @charTable(Position, ThisChar)
SELECT 1, SUBSTRING(@inputString, 1, 1) UNION ALL
SELECT 2, SUBSTRING(@inputString, 2, 1) UNION ALL
SELECT 3, SUBSTRING(@inputString, 3, 1) UNION ALL
SELECT 4, SUBSTRING(@inputString, 4, 1) UNION ALL
SELECT 5, SUBSTRING(@inputString, 5, 1) UNION ALL
SELECT 6, SUBSTRING(@inputString, 6, 1) UNION ALL
SELECT 7, SUBSTRING(@inputString, 7, 1) UNION ALL
SELECT 8, SUBSTRING(@inputString, 8, 1) UNION ALL
SELECT 9, SUBSTRING(@inputString, 9, 1) UNION ALL
SELECT 10, SUBSTRING(@inputString, 10, 1) UNION ALL
SELECT 11, SUBSTRING(@inputString, 11, 1) UNION ALL
SELECT 12, SUBSTRING(@inputString, 12, 1) UNION ALL
SELECT 13, SUBSTRING(@inputString, 13, 1) UNION ALL
SELECT 14, SUBSTRING(@inputString, 14, 1) UNION ALL
SELECT 15, SUBSTRING(@inputString, 15, 1) UNION ALL
SELECT 16, SUBSTRING(@inputString, 16, 1) UNION ALL
SELECT 17, SUBSTRING(@inputString, 17, 1) UNION ALL
SELECT 18, SUBSTRING(@inputString, 18, 1) UNION ALL
SELECT 19, SUBSTRING(@inputString, 19, 1) UNION ALL
SELECT 20, SUBSTRING(@inputString, 20, 1)
-- remove non-numerics inc. whitespace from the string
DELETE FROM @charTable
WHERE ThisChar NOT LIKE('[0-9]')
-- unfortunately this messes up the Position indicator,
-- so let's 'reset' this like so...
DECLARE @tempTable TABLE (
NewPosition INT IDENTITY(1,1),
OldPosition INT )
INSERT INTO @tempTable (OldPosition)
SELECT Position
FROM @charTable
ORDER BY Position ASC
UPDATE @charTable
SET Position = t2.NewPosition
FROM @charTable t1
INNER JOIN @tempTable t2 ON t1.Position = t2.OldPosition
-- now for every 2nd digit from the right of the numeric,
-- double it and store the result in the Doubled column
IF ( SELECT MAX(Position) % 2 FROM @charTable ) = 0 -- evens
BEGIN
UPDATE @charTable
SET Doubled = CAST(ThisChar AS TINYINT) * 2
WHERE Position % 2 <> 0
END
ELSE BEGIN -- odds
UPDATE @charTable
SET Doubled = CAST(ThisChar AS TINYINT) * 2
WHERE Position % 2 = 0
END
-- now if the doubled digit is > 9, sum the digits, else carry forward
-- to the Summed column. This goes for non-doubled digits too.
UPDATE @charTable
SET Summed =
CASE WHEN Doubled IS NULL
THEN CAST(ThisChar AS TINYINT)
WHEN Doubled IS NOT NULL AND Doubled <= 9
THEN Doubled
WHEN Doubled IS NOT NULL AND Doubled >= 10
-- sum the digits. Luckily SQL Server butchers int division...
THEN (Doubled / 10) + (Doubled - 10)
END
-- finally, sum the Summed column and if the result % 10 = 0, it's valid
IF ( SELECT SUM(Summed) % 10 FROM @charTable ) = 0
SET @result = 1
ELSE
SET @result = 0
RETURN @result
END
Para probar:
SELECT '371449635398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('371449635398431') [Valid Card?] UNION ALL
SELECT '3714 4963 5398 431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('3714 4963 5398 431') [Valid Card?] UNION ALL
SELECT '37XX XXXX 5398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('37XX XXXX 5398431') [Valid Card?] UNION ALL
SELECT 'This is not a valid string' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('This is not a valid string' ) [Valid Card?] UNION ALL
SELECT '1234123412341234' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('1234123412341234') [Valid Card?]
Fuente:
https://www.mssqltips.com/sqlservertip/3319/implementing-luhns-algorithm-in-tsql-to-validate-credit-card-numbers/
