Inderdaad geeft Google op “elfproef in SQL” diverse hits, waarvan de meest concrete toch wel [http://php.n3rd.nl/index.php/source/elf_proef_sql_server.sql] is. Als de functie door onze compilatie is gekomen, voeren we een paar BSN’s in. De eerste, die toevallig op een 0 eindigt, kwam er goed doorheen. De verbazing was groot toen onze eigen BSN’s niet aan deze elfproef bleken te voldoen! Gelukkig hebben we altijd wikipedia nog die ons vertelt dat er een VARIANT op de elfproef gebruikt moet worden:

Het burgerservicenummer bestaat uit 9 cijfers en voldoet aan een variant op de elfproef. De variant is in het laatste cijfer, dat in plaats van met 1, met -1 wordt vermenigvuldigd. Dit verschil is er opzettelijk ingebracht zodat een abusievelijk ingevoerd bankrekeningnummer als foutief wordt aangemerkt. Als het burger servicenummer wordt voorgesteld door ABCDEFGHI, dan moet

(9 × A) + (8 × B) + (7 × C) + (6 × D) + (5 × E) + (4 × F) + (3 × G) + (2 × H) + (-1 × I)

een veelvoud van 11 zijn.

In de op internet gevonden functie is dit aangepast, en wederom proberen we een aantal BSN’s. De functie blijkt niet te voldoen, omdat daarin steeds door 10 wordt gedeeld om het volgende cijfer te selecteren. Door afronding wordt dan een 1,9 net een 2, en we willen juist met de 1 gaan rekenen. Al doende bedenken we dat we alleen maar het laatste geselecteerde cijfer er eerst af moeten halen en dan door 10 delen. Dit blijkt afdoende te zijn om de functie correct te laten werken en vervolgens wordt het proces in gang gezet om de functie in gebruik te nemen.

Al met al een prima voorbeeld dat je nog steeds goed moet kijken of een functie van internet datgene doet wat jij wilt dat ie doet.
En dit is hem dan:

CREATE OR REPLACE FUNCTION FNELFPROEF(BSN_NR VARCHAR2)
RETURN VARCHAR2
AS
i INT := 1;
totaal INT := 0;
VAR_BSN INT;
BEGIN
VAR_BSN := TO_NUMBER(BSN_NR);
totaal := (-1*MOD(VAR_BSN,10));
VAR_BSN := (VAR_BSN - MOD(VAR_BSN,10))/10;
WHILE i < 9
LOOP
i := i+1;
totaal := totaal+(i*MOD(VAR_BSN,10));
VAR_BSN := (VAR_BSN - MOD(VAR_BSN,10))/10;
END LOOP;
IF MOD(totaal,11) = 0 AND totaal != 0 THEN
RETURN LPAD(BSN_NR, 9, '0');
ELSE RETURN NULL;
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN NULL;
END;

Kleine toelichting voor de hergebruiker: deze functie geeft dus het BSN zelf terug als het correct is, en NULL als het niet correct is of een niet-numerieke waarde bevat. Met LPAD wordt er een eventuele voorloopnul weer toegevoegd, omdat er geconverteerd is naar een NUMBER.

Rest ons nog de functie te verspreiden binnen Nederland door het volop delen van deze blog!

Wouter van Letht, Business Intelligence Consultant bij Pong BV
Pieter Hogeterp, Testanalist bij Newspark BV