# Get Luhn Check Digit in SQLRSS

## 3 replies

Last post Apr 05, 2018 01:01 PM by jimmy69

• KulerMaster

Member

110 Points

480 Posts

### Get Luhn Check Digit in SQL

I was wondering if you guys have a function that returns the Luhn's check digit for certain number e.g. credit card?

e.g. I want to assign 0100010000112341 to the param and then get 9 as result. I have this c# code but I'm not skilled enough to rewrite it in t-sql.

```private string GetLuhnCheckDigit(string number)
{
var sum = 0;
var alt = true;
var digits = number.ToCharArray();
for (int i = digits.Length - 1; i >= 0; i--)
{
var curDigit = (digits[i] - 48);
if (alt)
{
curDigit *= 2;
if (curDigit > 9)
curDigit -= 9;
}
sum += curDigit;
alt = !alt;
}
if ((sum % 10) == 0)
{
return "0";
}
return (10 - (sum % 10)).ToString();
}```

Thank you so much

• Deepak Panch...

Contributor

2930 Points

1210 Posts

### Re: Get Luhn Check Digit in SQL

Mar 28, 2018 09:57 AM|Deepak Panchal|LINK

Hi KulerMaster,

I suggest you to refer example below.

```CREATE FUNCTION dbo.fnGetLuhn
(
@Luhn VARCHAR(7999)
)
RETURNS VARCHAR(8000)
AS
BEGIN
IF @Luhn LIKE '%[^0-9]%'
RETURN @Luhn

DECLARE @Index SMALLINT,
@Multiplier TINYINT,
@Sum INT,
@Plus TINYINT

SELECT  @Index = LEN(@Luhn),
@Multiplier = 2,
@Sum = 0

WHILE @Index >= 1
SELECT  @Plus = @Multiplier * CAST(SUBSTRING(@Luhn, @Index, 1) AS TINYINT),
@Multiplier = 3 - @Multiplier,
@Sum = @Sum + @Plus / 10 + @Plus % 10,
@Index = @Index - 1

RETURN  @Luhn + CASE WHEN @Sum % 10 = 0 THEN '0' ELSE CAST(10 - @Sum % 10 AS CHAR) END
END```

Reference:

T-SQL Custom Function (SQL Server 2000)

Implementing Luhn's Algorithm in T-SQL to validate credit card numbers

The Luhn Algorithm in SQL

Regards

Deepak

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• KulerMaster

Member

110 Points

480 Posts

### Re: Get Luhn Check Digit in SQL

Hello Deepak,

Thank you so much for the code example. I found it as well but unfortunately, it's incorrect.

The following C# code snippet returns the correct value:

```public static int modulo10(string nummer)
{
// 'nummer' darf nur Ziffern zwischen 0 und 9 enthalten!

int[] tabelle = { 0, 9, 4, 6, 8, 2, 7, 1, 3, 5 };
int uebertrag = 0;

foreach (char ziffer in nummer)
uebertrag = tabelle[(uebertrag + ziffer - '0') % 10];

return (10 - uebertrag) % 10;
}```

How do I convert it to SQL?

Thank you so much

• jimmy69

Contributor

2183 Points

922 Posts