How to check Danish personal number

CREATE FUNCTION dbo.fnCheckDanSSN

(

@SSN CHAR(10)

)

RETURNS BIT

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.