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.