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

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

T-SQL (advanced custom function)

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
Asked:
fjkaykr11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Evan CutlerCommented:
Add this to your SQL Server Database as a saved function

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

Open in new window


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
 
fjkaykr11Author Commented:
@arcee123 it works!. Thanks for posting the full steps on how to run as well.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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