[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1030

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
fjkaykr11
1 Solution

Billing EngineerCommented:
0

Commented:

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

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

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.