Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Friday, May 29, 2009 2:26 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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
5/30/2009 8:15 AM | Saggi Neumann
Gravatar

# 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.
5/30/2009 9:41 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET