Peter Larsson Blog

Patron Saint of Lost Yaks

How to calculate Finnish personal number

CREATE FUNCTION dbo.fnCalculateFinSSN
(
            @SSN CHAR(10)
)
RETURNS CHAR(11)
AS
BEGIN
    IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9]'
        RETURN NULL
 
    IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0
        RETURN NULL
 
    IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0
        RETURN 0
 
    DECLARE @Digits INT
 
    SET     @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31
 
    RETURN @@SSN + SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1)
END