Solved

Posted on 2012-08-24
997 Views
This is a challenging question, so I can understand if I get no responses but I thought I would give a shot.  Thanks for any help you can give.
I have a 15 Digit customer number that I need to calculate a check digit for that value.
In order to come up with the check digit I use Excel (formula is below).
This formula is based on something called the Luhn Algorithm (I think that is the correct spelling).
The 15 digit customer number is stored in a SQL Server 2000 Database.
I would like to be able to get this calculation using a T-SQL Function rather than
Excel.  Does anyone know how is can do this with T-SQL on a SQL Server 2000 database

Excel Formula that will give me a check digit from a 15 digit number
=MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1),"00"),{1,2},1)),10)
0
Question by:fjkaykr11

LVL 142

Expert Comment

0

LVL 9

Accepted Solution

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

Then you can query your SQL from the db like this:

Select fnGetLuhn(<insert field here>) from TABLE;

And it SHOULD?? get you what you want.

Thanks
Evan
0

LVL 3

Author Closing Comment

@arcee123 it works!. Thanks for posting the full steps on how to run as well.
0

## Featured Post

### Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.