Function that Performs Credit Card Verification in SQL Server

Hello everyone,
In this article, I will try to give information about the function that performs credit card verification in SQL Server.
In some cases, you may want to perform credit card authentication in SQL Server.
You can easily do this using the function below.
CREATE FUNCTION dbo.KrediKartiDogrulamasiYapanFonksiyon(@inputString VARCHAR(20))
RETURNS TINYINT
AS
BEGIN
DECLARE @result TINYINT
IF @inputString NOT LIKE ('%[0-9]%[0-9]%[0-9]%')
RETURN 2
DECLARE @charTable TABLE (
Position INT NOT NULL
,ThisChar CHAR(1) NOT NULL
,Doubled TINYINT
,Summed TINYINT
)
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)
DELETE FROM @charTable
WHERE ThisChar NOT LIKE ('[0-9]')
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
IF (SELECT
MAX(Position) % 2
FROM @charTable)
= 0
BEGIN
UPDATE @charTable
SET Doubled = CAST(ThisChar AS TINYINT) * 2
WHERE Position % 2 <> 0
END
ELSE
BEGIN
UPDATE @charTable
SET Doubled = CAST(ThisChar AS TINYINT) * 2
WHERE Position % 2 = 0
END
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 THEN (Doubled / 10) + (Doubled - 10)
END
IF (SELECT
SUM(Summed) % 10
FROM @charTable)
= 0
SET @result = 1
ELSE
SET @result = 0
RETURN @result
END
--Kullanımı
SELECT DBO.KrediKartiDogrulamasiYapanFonksiyon('2233872595321982')
When you create and run the above function, you will see a result similar to the one below.
As you can see, we have verified the credit card number.
Good luck to everyone in business and life.