Home > Damm Algorithm in T-SQL > Damm Algorithm T-SQL Implementation

Damm Algorithm T-SQL Implementation

Recently I ended up having to actually write my own function returning the Damm algorithm check digit in T-SQL. I was sure I would find some Damm algorithm T-SQL code snippet on the net, but no, – algo implementation code examples can be found in literally any main stream languages but T-SQL. Here comes its T-SQL version now.

Note 1:

I used input/output Old/New Number as a string to make it work with alpha-numerics of a certain format, – hence I have some padding and prefixing and postfixing of the New Number as well as fixed Old Number format, which is, again, a varchar. This can easily be changed to numerics only, with no prefixes, postfixes or any of that stuff, if needed – simply take it off and change the input/output data types

Note 2:

I used this wiki for the reference of how this algo works: http://en.wikipedia.org/wiki/Damm_algorithm

CREATE FUNCTION dbo.CheckDigitDammAlgo (@vOldNumber NVARCHAR(6))
RETURNS NVARCHAR(15)
AS
BEGIN
DECLARE
@cPaddingLength tinyint = 6,    --constant padding length
@cPadding NCHAR(6) = '000000',    --padding constrant
@cPrefix  NCHAR(3) = 'ZX_',        --prefix constrant
@cPostfix NCHAR(4) = '_712',        --postfix constrant
@vNewNumber NVARCHAR(20);        --New Number (output)
DECLARE @vDigit tinyint;                --each of six Old Number digits
DECLARE @vDigitIndex tinyint = 1;        --Old Number digit position (1 through 6)
DECLARE @vFlagDone bit = 0;                --the outer while-loop ending flag
DECLARE  @vOldInterim tinyint
,@vNewInterim tinyint
,@vColumnIndex NVARCHAR(10)
,
@vRowIndex tinyint
,@vTableEntry tinyint;
DECLARE @vArray TABLE (
interim tinyint
,zero tinyint
,one tinyint
,two tinyint
,three tinyint
,four tinyint
,five tinyint
,six tinyint
,seven tinyint
,eight tinyint
,nine tinyint
);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (0, 0, 3, 1, 7, 5, 9, 8, 6, 4, 2);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (1, 7, 0, 9, 2, 1, 5, 4, 8, 6, 3);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (2, 4, 2, 0, 6, 8, 7, 1, 3, 5, 9);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (3, 1, 7, 5, 0, 9, 8, 3, 4, 2, 6);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (4, 6, 1, 2, 3, 0, 4, 5, 9, 7, 8);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (5, 3, 6, 7, 4, 2, 0, 9, 5, 8, 1);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (6, 5, 8, 6, 9, 7, 2, 0, 1, 3, 4);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (7, 8, 9, 4, 5, 3, 6, 2, 0, 1, 7);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (8, 9, 4, 3, 8, 6, 1, 7, 2, 0, 5);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (9, 2, 5, 8, 1, 4, 3, 6, 7, 9, 0);
IF ISNUMERIC(@vOldNumber) = 0
BEGIN
RETURN
NULL;
END

--pad @vOldNumber with zeroes to six characters
SET @vOldNumber = RIGHT(@cPadding + RTRIM(@vOldNumber), @cPaddingLength)
WHILE @vFlagDone = 0
BEGIN
SET
@vOldInterim = 0 --first OldInterim is always 0 for any digit
WHILE @vDigitIndex <= @cPaddingLength
BEGIN
SET
@vDigit = CONVERT(tinyint, SUBSTRING(@vOldNumber, @vDigitIndex, 1));
SET @vColumnIndex = CASE @vDigit
WHEN 0 THEN 'zero'
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
END;
SET @vRowIndex = @vOldInterim;
SELECT  @vTableEntry = CASE @vColumnIndex
WHEN 'zero' THEN zero
WHEN 'one' THEN one
WHEN 'two' THEN two
WHEN 'three' THEN three
WHEN 'four' THEN four
WHEN 'five' THEN five
WHEN 'six' THEN six
WHEN 'seven' THEN seven
WHEN 'eight' THEN eight
WHEN 'nine' THEN nine
END
FROM    
@vArray
WHERE   interim = @vRowIndex;
SET @vOldInterim = @vTableEntry;
SET @vDigitIndex = @vDigitIndex + 1
END
SET
@vFlagDone = 1
END
SET
@vNewNumber = @cPrefix + @vOldNumber + CONVERT(VARCHAR, @vTableEntry) + @cPostfix;
RETURN @vNewNumber
END

SELECT dbo.CheckDigitDammAlgo ('1')
SELECT dbo.CheckDigitDammAlgo ('2')
SELECT dbo.CheckDigitDammAlgo ('3')
SELECT dbo.CheckDigitDammAlgo ('4')
SELECT dbo.CheckDigitDammAlgo ('5')
SELECT dbo.CheckDigitDammAlgo ('6')
SELECT dbo.CheckDigitDammAlgo ('7')
SELECT dbo.CheckDigitDammAlgo ('8')
SELECT dbo.CheckDigitDammAlgo ('9')
SELECT dbo.CheckDigitDammAlgo ('10')

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: