Peter Larsson Blog

Patron Saint of Lost Yaks

How to check Danish personal number

CREATE FUNCTION dbo.fnCheckDanSSN
(
            @SSN CHAR(10)
)
RETURNS BIT
AS
BEGIN
    IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
        RETURN 0
 
    DECLARE @x CHAR(6)
   
    SET     @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2)
 
    IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0
        RETURN 0
 
    IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0
        RETURN 0
 
    IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0
        RETURN 0
 
    IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '57' AND ISDATE('20' + @x) = 0
        RETURN 0
 
    IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '58' AND '99' AND ISDATE('18' + @x) = 0
        RETURN 0
 
    DECLARE @Digits INT
 
    SET     @Digits = 4 * SUBSTRING(@SSN, 1, 1) + 3 * SUBSTRING(@SSN, 2, 2) + 2 * SUBSTRING(@SSN, 3, 2) + 7 * SUBSTRING(@SSN, 4, 2)
                                     + 6 * SUBSTRING(@SSN, 5, 2) + 5 * SUBSTRING(@SSN, 6, 2) + 4 * SUBSTRING(@SSN, 7, 2)
                                     + 3 * SUBSTRING(@SSN, 8, 2) + 2 * SUBSTRING(@SSN, 9, 2) + 1 * SUBSTRING(@SSN, 10, 2)
 
    RETURN 1 - SIGN(@Digits % 11)
END

Legacy Comments


Peso
2009-05-30
re: How to check Danish personal number
Which is another variant of this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195

The algorithm is called Luhn and the implementation in the link above is much faster.