Archive

Posts Tagged ‘Damm Algorithm SQL’

Damm Algorithm T-SQL Implementation

November 7, 2014 Leave a comment

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