# Thinking outside the box

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

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

## #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

## #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.