Thinking outside the box

# 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

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