T-SQL (advanced custom function)

Posted on 2012-08-24
Last Modified: 2012-08-24
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
Question by:fjkaykr11
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    LVL 9

    Accepted Solution

    Add this to your SQL Server Database as a saved function

    CREATE FUNCTION	dbo.fnGetLuhn
    	@Luhn VARCHAR(7999)
    	IF @Luhn LIKE '%[^0-9]%'
    		RETURN @Luhn
    		@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

    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.

    LVL 3

    Author Closing Comment

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

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    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.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now