locked
Get Luhn Check Digit in SQL RRS feed

  • Question

  • User1536465747 posted

    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 <g class="gr_ gr_344 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="344" data-gr-id="344">in t</g>-<g class="gr_ gr_345 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="345" data-gr-id="345">sql</g>.

    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

    Tuesday, March 27, 2018 12:03 PM

All replies

  • User347430248 posted

    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

    Wednesday, March 28, 2018 9:57 AM
  • User1536465747 posted

    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

    Thursday, April 5, 2018 11:07 AM
  • User632428103 posted

    Hello all,

    @kulerMaster => never try to implement this but i have found a link who can help you to solve your problem

    http://www.sqlservercentral.com/scripts/Miscellaneous/30602/

    Thursday, April 5, 2018 1:01 PM