Microsoft SQL Server T-SQL UDF Luhn Function
Here's my first attempt at writing a UDF in T-SQL for Microsoft SQL Server 2000. We had a third party application that used a Microsoft SQL server backend. The application couldn't be customized but we needed a checkdigit added to the database when it added a record. To do this, I hacked out this ugly luhn function and then added some triggers to the table.
Sample Trigger:
This function will return the checkdigit for whatever argument you pass. For more information about the Luhn function, also known as Mod 10, see the Wikipedia article.
Download Luhn Function
Sample Trigger:
CREATE Trigger AutoCheckDigitInsert
ON dbo.tablename
FOR INSERT
AS
UPDATE [tablename] SET checkdigit = dbo.luhn(isonumber.isonumber+''+tablename.idnumber) FROM
tablename, isonumber, inserted WHERE tablename.checkdigit IS null) AND (tablename.idnumber = inserted.idnumber)
This function will return the checkdigit for whatever argument you pass. For more information about the Luhn function, also known as Mod 10, see the Wikipedia article.
Download Luhn Function
CREATE FUNCTION luhn (@s varchar(20) )
RETURNS int
AS
BEGIN
DECLARE @i int, @j int, @total int, @final int, @tmp int, @result bit
SELECT @i = 2
SELECT @final = 0
SELECT @total = 0
SELECT @s =ltrim(@s)
SELECT @s =rtrim(@s)
WHILE @i < (len(@s) + 2) BEGIN SELECT @j=(ASCII(substring(@s,@i-1,1))-48) IF @i % 2 = 0 SELECT @j = @j * 2 IF @j > 9
SELECT @j = @j - 9
SELECT @tmp = @total
SELECT @total=@tmp+@j
SELECT @i=@i+1
END
IF @total % 10 = 0
SELECT @final = 0
ELSE
SELECT @final = 10 - (@total % 10)
RETURN (@final)
END
Thanks for your indirect help. The logic in your post helped me solve a problem for my homework project in a databases course I take. Much appreciated.
Posted by Anonymous | 3:44 PM
Thank you !
Posted by Anonymous | 12:35 PM
Just got me out of a 'sticky' corner... many thanks
Posted by Anonymous | 7:58 AM
You can slightly modify your function so it will be generic and work with any number of digits.
To do this, you have to start from th end of the string
Here is the example:
CREATE FUNCTION LuhnSum (@s varchar(20) )
RETURNS int
AS
BEGIN
DECLARE @i int, @j int, @total int, @final int, @tmp int, @result bit, @testMsg varchar(50)
DECLARE @oddness INT
SELECT @final = 0
SELECT @total = 0
SELECT @s =ltrim(@s)
SELECT @s =rtrim(@s)
SELECT @i = len(@s)
SELECT @oddness = @i % 2
IF @oddness = 0
SELECT @oddness = 1
ELSE
SELECT @oddness = 0
WHILE @i > 0
BEGIN
SELECT @j=(ASCII(substring(@s, @i, 1))-48)
IF @i % 2 = @oddness
SELECT @j = @j * 2
IF @j > 9
SELECT @j = @j - 9
SELECT @tmp = @total
SELECT @total=@tmp+@j
SELECT @i=@i-1
END
IF @total % 10 = 0
SELECT @final = 0
ELSE
SELECT @final = 10 - (@total % 10)
RETURN (@final)
END
Mitek
Cheers,
Posted by Anonymous | 12:35 AM
Mitek:
Everything is ok.. but
IF @oddness = 0
SELECT @oddness = 1
ELSE
SELECT @oddness = 0
is not required.
Cheers,
-Yogee
Posted by Anonymous | 11:06 PM