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

By:   |   Comments (16)   |   Related: More > Functions User Defined UDF

##### Problem

You are tasked with checking account or credit card numbers for validity, for example as part of a web application handling sensitive card data. You wish to implement this as a function within SQL Server, with the ability to be called with any number and return either TRUE (valid) or FALSE (invalid). Alternatively, you are looking for a method to validate a number based on a check digit: you could be using this as part of a password reset check, for account verification or similar purposes.

##### Solution

The industry standard for checking credit card numbers for validity is known in colloquial terms as the 'MOD 10 Check', or more formally as Luhn's Algorithm. It has no cryptographic validity, but it is a useful rule-of-thumb check that can be used to validate that a card number is correct, and can be used in its opposite form to generate account (and credit card) numbers - to help discourage potential fraudsters, this function is NOT covered here.

Much literature already exists on Luhn's Algorithm, links for which are provided in the Next Steps section at the end of this tip. The algorithm is simple, but powerful. In the following sections I will provide a step-by-step guide to validating these numbers by hand, then show how we can replicate this as a User-Defined Function (UDF) within SQL Server. I will also show you a method to remove 'noise' from the number - for example, spaces or leading/trailing characters.

One final note - any card numbers used here for illustration / example are fictitious - these pass the MOD 10 check, but are not real credit card numbers, only samples. The samples are from PayPal and may be found here: www.paypalobjects.com/en_US/vhelp/paypalmanager_help/credit_card_numbers.htm

## Algorithm Overview

The algorithm can be broken down into 4 stages. All stages are carried out using simple mathematics and rules.

#### Stage 1: N * 2 for every 2nd N from the right

Let's first get a number which we are testing for validity and split it out into its component digits, in a format we can easily understand. The number we will use is an AMEX sample card number: The first step is to apply N*2 to every 2nd digit starting from the right. This is true regardless of the length of the number (odd or even number of digits), and is because the final digit is normally a check digit. Let's do this now. #### Stage 2: If (N*2) > 9 sum the digits, else carry the digits

In this stage, we carry forward ALL the digits from either the original number or N*2 to produce a final number for verification. The rule is: If the number was doubled in Stage 1 AND this number > 9, then sum the digits. So if the original number was 9, for example, and was selected for doubling in Stage 1 to become 18, we now need to add 1 + 8 = 9 since 18 > 9. Likewise, if the number 2 was doubled in Stage 1 to equal 4, because 4 < 9 we do not need to transform it further. All other digits - i.e. those NOT selected in Stage 1 - are carried forward without transformation.

This is easier to show than describe. Like so: So now we have a final number that we can check for validity.

#### Stage 3: Sum the digits

This stage is simple - take this output number and sum the digits. This results in a single output integer we'll call X, in this case 80. We now need to test this number - more formally, IF X % 10 = 0 THEN TRUE ELSE FALSE. For those of you unfamiliar with the MOD operation, it's almost interchangeable with the term 'is divisible by'. When we MOD two numbers, we fetch the remainder of the division operation between the two numbers. So, 10 MOD 2 = 0 because 2 evenly divides into 10 with no remainder. 250 MOD 30 = 10, because 250 is divisible by 30 a total of 8 times (240) with 10 remainder. In SQL and in mathematics, MOD is represented by the percentage (%) sign.

#### Stage 4: IF X MOD 10 = 0 THEN TRUE ELSE FALSE

Applying this then, we know that 80 MOD 10 = 0, therefore the card number is valid. For balance, let's show the same grid, but this time using an invalid card number. You can see the algorithm quickly generates an output X that fails the MOD 10 check. ## Rewriting this for SQL Server

Let's now turn our attention to rewriting this in T-SQL.

The code we write must be fairly efficient, but we're constrained in a unique way by using T-SQL - we're writing in the language of relational set theory, not in object oriented language. So whereas in Python or C we might implement the card number as an array of numbers then apply transformations to all elements of the array, we don't have that luxury in SQL Server. What we CAN do, however, is use a temporary table variable to the same effect.

We must also cleanse the card number and make sure we don't have any trailing spaces. In the real world, we'll receive a number for testing as a string, or VARCHAR of indeterminate length, so we'll work on this basis rather than using an INT/BIGINT.

Luckily we can combine this and use a temporary table variable both for cleansing the number and for validity checking.

Let's now set up the function header, and put in a couple of simple constraints - the function can accept a string no longer than 20 characters, and if there aren't at least three digits present in the string, return an error code:

```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
```

Now, we're going to use a table variable to handle the calculation.

```-- set up our table for algorithm calculation

DECLARE @charTable TABLE (
Position INT NOT NULL,
ThisChar CHAR(1) NOT NULL,
Doubled TINYINT,
Summed TINYINT )
```

There's a neat trick we can use now. The instinctive approach to parsing this string is to step through it one character at a time, removing whitespace and non-numeric data. However, we're in a set-based environment. Neither do we want to encapsulate the string in many string-parsing functions like SUBSTRING, REPLACE, CHARINDEX and the like, as this will get very messy very quickly, and our key tool LIKE isn't rich in functionality compared to the non-database equivalent functions.

For this reason, we'll pivot the string into a table, each row containing one character. Using this methodology, we can quickly eliminate unwanted characters while retaining order, and additionally we can use multiple columns for our calculations.

```-- 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)
```

We could have also achieved this with a WHILE loop instead, but this way is also valid and a loop hardly seems worthwhile for 20 characters.

So now let's clean up the data.

```-- remove non-numerics inc. whitespace from the string

DELETE FROM @charTable
WHERE  ThisChar NOT LIKE('[0-9]')
```

As we all know, DELETing from a table with an IDENTITY column doesn't reset the identities in that table to become continuous - we land up with holes in the identity sequence. So, for example, if we had five rows in a table with identities 1-5 and we DELETE the row with identity 4, we don't have a table with identities 1-4 - we have a table with identities 1-3 and 5.

We need to fix this, as we're going to use this measure to work out which rows to calculate upon based on whether they are odd or even. And this opens a real can of worms - my first attempt to do this was met with protests by SQL Server that an UPDATE is side-effecting and not allowed in functions. I came up with this:

```-- 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
```

## Bonus Material

If you're interested, here's my original version. It uses a Common Table Expression to expose a rendition of the table with a corrected version of the position, then joins the tables using the original positions.

```;WITH Repositioned (NewPosition, OldPosition) AS (
SELECT      ROW_NUMBER() OVER ( ORDER BY Position ) [NewPosition],
Position [OldPosition]
FROM  @charTable )
UPDATE            t1
SET               t1.Position = t2.NewPosition
FROM        @charTable t1
INNER JOIN  Repositioned t2 ON t1.Position = t2.OldPosition
```

Also, in the main code, it's worth noting that in functions you cannot issue a statement that changes the database - a function is designed to return values. This extends to temporary objects too, even though they're allowed. Confused? You'll see that in this segment:

```UPDATE  @charTable
SET   Position = t2.NewPosition
FROM  @charTable t1
INNER JOIN  @tempTable t2 ON t1.Position = t2.OldPosition
```

I have specified to update @charTable rather than the alias t1. UPDATE statements on table variables are allowed in functions, but aliases will fail.

## Calculations

Now let's do our calculations.

```-- 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
```

We can take advantage of SQL Server's unwillingness to divide two INTs correctly (the result is reported as an INT) to simplify our equation:

```-- 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
```

And now let's define our validity conditions based on the modulo output.

```-- 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
```

Let's take a look at the working table. My code doesn't expose this as there's no need - we're only interested in the output value. But for curiosity: ## Complete Script

Here is the complete T-SQL code.

```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
```

## Testing

Here is a test script that you can use:

```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?]
```

Here's how to call the function, and some test results using one of our sample card numbers discussed earlier and some other input. Recall that output 1 = valid, output 0 = invalid, output 2 = error/bad string: ##### Next Steps Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.

View all my tips

 Thursday, March 8, 2018 - 6:34:52 AM - Andy Back To Top (75373) Hi Derek Can't thank you enough for this post! Really gave me a breakthrough with one of the data integrity issues I was struggling with and will put this to good use in the future. Stlll relvatively new to SQL after many years in MS Access and VBA, but always wanted to take it to te next level. Your use of this function in a stored procedure has been a big boost to my knowledge (even though I don't yet understand how you did it) Thank you for sharing this  Best Regards   Andy

 Wednesday, February 28, 2018 - 9:50:06 AM - Howard C. Shaw III Back To Top (75317) Your calculation of (Doubled/10)+(Doubled-10) seems unnecessary to me. Your case statement, and the fact you are already operating on doubled values of single digits, means your domain is simply (10,12,14,16,18), right? For each of these, Doubled-9 gives the same answer, without the extra division operation: 10-9 = 1 12-9 = 3 14-9 = 5 16-9 = 7 18-9 = 9

 Tuesday, January 6, 2015 - 5:08:42 PM - Amos Back To Top (35847) Hello Derek,      Following my previous post I would to present a shorter code of a user function to check if a credit card number is llegal/illegal     The stages 1-3 are done in one loop. We're working on the string that represents the credit card number as an array. We take only the characters that represent digits and we make the calculations on them. We start from the rightmost digit to the leftmist one. In each loop iteration we multiply the current digit by 1 or 2. The current multiplier 1 or 2 is calculated by subtracting the previous one  from 3. For example if the previous multiplier is  1, the current one is 2, because 3-1=2. Another thing, we don't check if multiplying the digit by 2 yields a single digit/two digits  number. We allways calculate the quotient and the reminder of the division of the product by 10. If we divide one digit number by 10 - the quotient is 0 and the reminder is the digit itself and the sum is the digit itself.     Hereby a shorter version of  the user defined function: create function dbo.fn_CheckCreditCardNumber(@inputStr varchar(25))    returns tinyint-- parameters: @inputStr  varchar(25)-- outputs: tinyint: 2 – Credit card number contains--                       less than 13 digits--                   0 – Legal credit card number   --                   1 – Illegal Propre credit card number   as begin-- The number of digits of a credit card number must be 13 at least  if(@inputStr not like ('%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]'))    return 2    declare @strLength   smallint,              @multiplier    tinyint,              @sum           tinyint,              @prod           tinyint,              @i                tinyint,              @result         tinyint,             @digit            char(1)    set @strLength  = len ( @inputStr )-- Calculating the original number's string length for the loop    control    set @i          = @strLength    set @multiplier = 1   set @sum        = 0    while(@i >= 1)   begin   set @digit = substring(@inputStr, @i, 1)   if(@digit like '[0-9]')-- A digit   begin      set @prod = convert(tinyint , @digit) * @multiplier      set @sum  = @sum + @prod/10 + @prod % 10        set @multiplier = 3 - @multiplier      end       set  @i = @i - 1   end -- If the digits' sum modolo 10 equals 0 - divisible-- without reminder by 10 - the number is legal   if(@sum %10 = 0)-- Legal credit card number     set @result = 1    else-- Illegal credit card number      set @result = 0    return @resultend

 Sunday, December 21, 2014 - 12:34:04 AM - Amos Back To Top (35705) Hello Derek,     I've seen only now your tip.     As an sql developer, I would like to say rhat it is a nice tip. You are making an efficient use with thetool that SQL Server gives us - the user defined functions.     You have specified in your article, that in SQL Server we don't have the luxury ofusing array to store the digits of the credit card number. What we have to do is usinga temporary table to the same effect.    I would like to say, that you can use a string in order to simulate an array of digits. You can refer to each one of its eelements by substring.   Hereby a function I've written to check ig a check digit is correct. If you've any comments, let meknow: create function dbo.fn_CheckCreditCardNumber(@inputStr varchar(25))    returns tinyint-- parameters: @inputStr  varchar(25)-- outputs: tinyint: 2 – Credit card number contains--                       less than 13 digits--                   0 – Legal credit card number   --                   1 – Illegal Propre credit card number   as begin  declare @workStr   varchar(25)  declare @strLength smallint  declare @i         smallint  declare @sum       smallint  declare @prod      smallint  declare @digit     char(1)  declare @evenInd   tinyint  declare @result    tinyint-- The number of digits of a credit card number must be 13 at least  if(@inputStr not like ('%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]')) return 2-- Moving the digits only from the input string to the -- "digits' array" - A string-- Calculating the original number's string length for the loop control   set @strLength = len ( @inputStr )   set @i = 1   set  @workStr = ''   while(@i    begin  set @digit = substring(@inputStr, @i, 1)  if(@digit like '[0-9]')-- A digit-- Adding the digit character to the "digits' array"  set @workStr = @workStr + @digit        set @i = @i +1   end-- Additional pass on the "digits' array". If the digits number is ---- even, each digit in an odd position is multiplied by 2 and the -- current position in the array  is changed with the product.-- Otherwise, each digit in an odd place is multiplied by 2-- and the current position in the array is change with the product. -- If the product is more than 9, its digits are sum. The curerent ---- position in the array is changed with this sum.-- The first, second and the third steps are done in one loop.    set @sum     = 0    set @evenInd = 0    if(@strLength % 2 = 0)     set @evenInd = 1    set @i = 1    while(@i     begin  set @digit = substring(@workStr, @i, 1)-- Getting the current digit in the "array"  set @prod   = convert(smallint , @digit)  if(@evenInd = 1 and @i % 2 = 1 or               @evenInd = 0 and @i % 2 = 0)            set @prod = @prod * 2  if(@prod >= 10)-- The product by 2 greater than 9  begin-- Summing the two digits of the product   set @prod = @prod/10 + @prod-10   end-- Updating the digits' sum  set @sum = @sum + @prod  set @i = @i +1  end -- If the digits' sum modolo 10 equals 0 - divisible-- without reminder by 10 - the number is proper  if(@sum %10 = 0)     set @result = 1 –  Legal credit card number  else   set @result = 0 –  Illegal credit card number   return @resultend Hope to read your opinion Thank you,    Amos

 Tuesday, September 2, 2014 - 5:12:01 AM - Henn Back To Top (34347) I have create something similar for checking PIN validation in Excel. Unfortunately this article is only in estonian but the meaning is readable. at least similar techique works (single formula) this formula checks cyymmddnnnx 11 number PIN where c is gender+century (1-3-5 male, 2-4-6 female, 1-2 1800, 3-4 1900, 5-6 2000), yy year, mm month, dd day nnn unique number on day and x checksum using standard algorithm the article is there    https://sarviktaat.wordpress.com/2012/12/06/isikukoodi-valideerimine-excelis/ and the formula is there: =IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN(\$A\$1:\$J\$1);1)+0);(MID("1234567891";COLUMN(\$A\$1:\$J\$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN(\$A\$1:\$J\$1);1)+0);(MID("3456789123";COLUMN(\$A\$1:\$J\$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN(\$A\$1:\$J\$1);1)+0);(MID("1234567891";COLUMN(\$A\$1:\$J\$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale")) "isikukood vigane" means wrong date numbers "isikukood õige" means everything is valid "kontrolljärk vale" means checksum isn't valid   this isn't actually "SQL Tip" but might be interesting to research :) Henn

 Thursday, August 28, 2014 - 11:21:36 AM - Malcolm Back To Top (34321) Thanks for the updated select statement, I never knew you could do that.

 Thursday, August 28, 2014 - 11:10:39 AM - Scott Back To Top (34318) That is too many steps.  You can condense the whole calculation to one query. ```SELECT @result = 1 - SIGN(SUM(val3) % 10) FROM ( SELECT Position = ROW_NUMBER() OVER (ORDER BY number DESC), val1 = CAST(ThisChar AS TINYINT) FROM ( SELECT number, ThisChar = SUBSTRING(@inputString, number, 1) FROM master.dbo.spt_values WHERE type = 'P' AND number BETWEEN 1 AND LEN(@inputString) ) AllChars WHERE ThisChar BETWEEN '0' AND '9' ) Digits CROSS APPLY ( SELECT val2 = val1 * (2 - Position % 2) ) x CROSS APPLY ( SELECT val3 = val2 % 10 + val2 / 10 ) y;```

 Thursday, August 28, 2014 - 6:37:01 AM - Magnus Back To Top (34305) `SELECT CardNo, [dbo].[usp_LuhnsAlgorithm_New](CardNo) [Valid Card?]  from MyTable` should give you two result columns: the list of card numbers and the result of the function for each card number.

 Thursday, August 28, 2014 - 4:30:03 AM - Malcolm Back To Top (34303) I see how SELECT [dbo].[usp_LuhnsAlgorithm_New]('37XX XXXX 5398435') [Valid Card?] would work but haw can you select more than one card number from your table so say something like SELECT [dbo].[usp_LuhnsAlgorithm_New](select CardNo from MyTable) [Valid Card?] though I understand that the above does not work, but how can I get the same result? Thanks

 Wednesday, August 27, 2014 - 2:04:55 PM - m mcdonald Back To Top (34291) Nice article however, agree with Brent's assertion/concern...   Interesting side note:  37XX XXXX 5398435 will test as valid...

 Wednesday, August 27, 2014 - 1:23:18 PM - Brent Ozar Back To Top (34288) Jeremy - you're missing the point. If unencrypted credit card numbers made it into the database layer, then they're a huge security risk. Think Profiler trace or plan cache query.

 Wednesday, August 27, 2014 - 11:42:49 AM - Jeremy Kadlec Back To Top (34286) Thanks everyone for the feedback.  I think the point of this tip was to show how to validate credit numbers with a function, not about storing credit numbers in a SQL Server database. Thank you,Jeremy KadlecCommunity Co-Leader

 Wednesday, August 27, 2014 - 11:25:55 AM - Tibor Nagy Back To Top (34283) Interesting article but I agree with Brent. You should not have card numbers in your DB. A stored procedure to check the card number when it is entered can be interesting but it is better if the clear card number does not reach the DB.

 Wednesday, August 27, 2014 - 8:35:42 AM - Nitin Back To Top (34281) We only hold the last 4 digits in a DB plus the authorization tocken that was generated when the card was validated. Having said that I found this article very interesting and will put it into my KB for sure. Good work.

 Wednesday, August 27, 2014 - 8:19:39 AM - Brent Ozar Back To Top (34280) This is an interesting tip, but I'd zoom out a little and ask, why on earth would you ever want to let unencrypted credit card numbers get into the database layer? That's a huge, huge, HUGE security no-no.