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


Saggi Neumann
2009-05-30
re: How to check Danish personal number
This is how we validate ID numbers in Israel. Cheers! :-)

CREATE FUNCTION dbo.fnIsValidIsraeliID (@IDNumber INT)

RETURNS BIT

AS

BEGIN
DECLARE @ret BIT;
DECLARE @IDstr VARCHAR(10);
SET @IDstr = CAST(@IDNumber AS VARCHAR(10));
SET @IDstr = REPLICATE('0',9-LEN(@IDstr)) + @IDstr
DECLARE @chksum TABLE (idx TINYINT, val TINYINT);
INSERT INTO @chksum
SELECT 1,1
UNION ALL SELECT 2,2
UNION ALL SELECT 3,1
UNION ALL SELECT 4,2
UNION ALL SELECT 5,1
UNION ALL SELECT 6,2
UNION ALL SELECT 7,1
UNION ALL SELECT 8,2

IF @IDNumber % 10 =
(SELECT 10 - (SUM(val2) % 10) as val
FROM
(SELECT CASE WHEN val>9 THEN (val / 10)+ (val % 10) ELSE val END val2
FROM
(
SELECT CAST(SUBSTRING(@IDstr,chksum.idx,1) AS TINYINT) * chksum.val AS val
FROM @chksum AS chksum)
p1)
p2)
SET @ret = 1;
ELSE
SET @ret = 0;
RETURN @ret;
END

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.