T-SQL: Numer Pesel bez tajemnic

Pracując w Polsce z bazami danych, prędzej czy później spotkamy się z problemem walidacji różnych numerów (NIP,REGON,PESEL,numer konta bankowego, itd). Na przykładzie numeru PESEL postaram się pokazać jak sobie z tym radzić po stronie Bazy danych. Niestety na większość z podanych numerów nie można nałożyć twardej walidacji (np. Niektóre numery PESEL były błędnie wygenerowane, obcokrajowcy nie posiadają PESEL, itd). Twarda walidacja oznacza iż nie możemy zabronić zapisania niepoprawnej wartości w systemie. Reperkusją takiego zachowania jest dopuszczenie błędnie wprowadzonych numerów na podstawie prawidłowych danych. Z kolei raportowanie,obróbka powinny być oparte o dane poprawne. Numery PESEL mają stosunkowo prostą strukturę: Składają się z samych cyfr, mają określoną długość,niektóre cyfry mają określone znaczenie.
Zaczynając od początku wiemy iż numer PESEL winien miec 11 znaków(cyfr). w TSQL mamy do dyspozycji funkcję isnumeric oraz len.

select isnumeric('11111111111'),len('11111111111')

W ten sposób możemy określić czy ciąg jest liczbą/składa się z cyfr oraz czy ma długość 11 znaków.

Niestety nie jest to wystarczające sprawdzenie. Osoby które projektowały numer PESEL, wymyśliły także pewien mechanizm ochronny, tak zwaną cyfrę kontrolną. W numerze PESEL jest to ostatni znak, który jest obliczany na podstawie pierwszych 10 znaków. Algorytm jest prosty każda kolejna cyfra posiada swoją wagę przez która jest mnożona. Z sumy iloczynów obliczane jest modulo (reszta z dzielenia przez 10) wynik jest liczbą kontrolną.
Poniżej kawałek funkcji która sprawdza obliczoną sumę kontrolną z tą podaną. Jako że taki kod w zapytaniu już wygląda dość kiepsko, warto skorzystać z funkcji w TSQL i zdefiniować swoją własną funkcję do walidacji.
if (
( ( cast(substring(@psl,1,1) as bigint)*9)
+(cast(substring(@psl,2,1) as bigint)*7)
+(cast(substring(@psl,3,1) as bigint)*3)
+(cast(substring(@psl,4,1) as bigint)*1)
+(cast(substring(@psl,5,1) as bigint)*9)
+(cast(substring(@psl,6,1) as bigint)*7)
+(cast(substring(@psl,7,1) as bigint)*3)
+(cast(substring(@psl,8,1) as bigint)*1)
+(cast(substring(@psl,9,1) as bigint)*9)
+(cast(substring(@psl,10,1) as bigint)*7) ) % 10
= right(@psl,1) )
begin
return 1;
end

A cała funkcja może wyglądać następująco:

create FUNCTION [dbo].[is_pesel]
(@PESEL varchar(255))
RETURNS int
AS
BEGIN
declare @psl varchar(255);
set @psl=0;
if @PESEL='00000000000' begin return 0 end
if @PESEL='12345678910' begin return 0 end
if @PESEL='11111111116' begin return 0 end
if @PESEL='11111111123' begin return 0 end
if SUBSTRING(@pesel,3,1) not in ('0','1','2','3') begin return 0 end
if (isnumeric(@PESEL) =1 )
begin
set @psl = cast(@PESEL as varchar(255));
end
else
begin
return 0;
end;

if (
( ( cast(substring(@psl,1,1) as bigint)*9)
+(cast(substring(@psl,2,1) as bigint)*7)
+(cast(substring(@psl,3,1) as bigint)*3)
+(cast(substring(@psl,4,1) as bigint)*1)
+(cast(substring(@psl,5,1) as bigint)*9)
+(cast(substring(@psl,6,1) as bigint)*7)
+(cast(substring(@psl,7,1) as bigint)*3)
+(cast(substring(@psl,8,1) as bigint)*1)
+(cast(substring(@psl,9,1) as bigint)*9)
+(cast(substring(@psl,10,1) as bigint)*7) ) % 10
= right(@psl,1) )
begin
return 1;
end

return 0;
END

Funkcja z założenia wraca wartość 1 w przypadku gdy numer jest prawidłowy(a właściwie jego suma kontrolna jest prawidłowa) w przeciwnym wypadku zwraca 0. Plus dodatkowo weryfikuje kilka dodatkowych założeń. Sprawdza czy podana wartość nie należy do tak zwanych wartości skompromitowanych, znanych kombinacji z poprawną sumą kontrolną. W dużych zbiorach wartości skompromitowane można łatwo wykryć odpytując bazę i sortując po ilości wystąpień danego numeru. Kolejne założenie jakie zostało zweryfikowane to iż w bazie powinny być PESELe osób żyjących (czyli takich które się już urodziły) dodatkowo też założono że w bazie nie znajdują się PESELe osób urodzonych przed 1900 rokiem.

if SUBSTRING(@pesel,3,1) not in ('0','1','2','3') begin return 0 end

Ten fragment kodu pozwala nam zweryfikować czy pierwsza liczba miesiąca urodzenia to wartości 0,1,2,3
Dla osób urodzonych od roku 2000 do liczby oznaczającej numer miesiąca dodaje się wartość 20, tak więc można by jeszcze dodatkowo ograniczyć ten zbiór… Można jeszcze dodatkowo sprawdzić czy 5 i 6 cyfra razem mieszczą się pomiędzy 01 a 31.

Kolejną informacją zawartą w numerze PESEL jest płeć, aby pobrać płeć z numeru PESEL będziemy potrzebować następującego kodu:

create FUNCTION [dbo].[pesel_plec]
(
@PESEL varchar(255)
)
RETURNS int
AS
BEGIN
declare @plec int;
declare @psl varchar(255);
set @psl=0;
if (is_pesel(@PESEL) =1 )
begin set @psl = cast(@PESEL as varchar(255)); end
else
begin return -1; end;
if (cast((SUBSTRING(@psl,10,1))as bigint) in (0,2,4,6,8))
begin
-- kobieta
set @plec= 0; end else
begin
--facet
set @plec = 1; end;
return @plec;
END

Najpierw sprawdzamy czy numer PESEL jest prawidłowy, kolejno sprawdzamy 10 cyfrę w numerze PESEL jeśli cyfra jest parzysta (0,2,4,6,8) to numer należy do kobiety, w przeciwnym wypadku mamy do czynienia z mężczyzną. Funkcja zwraca 0 – kobieta lub 1 – mężczyzna.

Jak wiadomo pierwsze 6 cyfr to datau rodzenia w formacie rrmmdd. Znając tą datę możemy obliczyć wiek osoby. By to uczynić można użyć poniższego kodu T-SQL:

CREATE FUNCTION [dbo].[get_wiek_from_pesel]
( @pesel varchar(11))
RETURNS varchar(255)
AS
BEGIN

if (dbo.is_pesel(@pesel) != 1 )
begin return -1 end
declare @data varchar(255);
declare @wynik int;

select @data = case
when substring(i,3,1)=2 then
(substring(i,5, 2) + '-'+'0' +substring(i,4,1)+'-'+'20'+substring(i,1,2))
when substring(i,3,1)=3 then
(substring(i,5, 2)+'-'+'1' + substring(i,4,1)+'-'+'20'+substring(i,1,2))
else
(substring(i,5, 2)+'-'+substring(i,3,2) + '-'+'19' +substring (i,1,2))
end
from (select @pesel as i ) e;
SELECT @wynik= FLOOR(DATEDIFF(DAY, convert(date,@data,105), getdate()) / 365.25)
return @wynik
END

Tradycyjnie najpierw sprawdzamy poprawność numeru PESEL, Tutaj przyda się weryfikacja wartości skompromitowanych oraz weryfikacja pierwszych 6 cyfr czy rzeczywiście reprezentują one datę urodzenia.
Powyższa funkcja ‘skleja’ datę na podstawie znanych informacji. Kolejno jest obliczana liczba dni pomiędzy dziś a złożoną datą urodzenia, wynik dzielony jest przez 365 i 1/4 dnia (uwzględnienie lat przestępnych). W ten prosty sposób z dość dużą dokładnością mamy obliczony wiek.

T-SQL – Definiowanie własnych funkcji

Dzisiejsze RDBMS w wiekszosci pozwalaja na definiowanie sowich wlasnych funkcji, w przypadku SQL Server mamy do dyspozycji rozszerzenie SQL nazywane Transact SQL (T-SQL). Aby w TSQL utworzyć funkcję potrzebujemy poniższy kod:

CREATE FUNCTION [dbo].nazwa_funkcji
(
@argument varchar(255) --nazwy argumentów po przecinku
)
RETURNS int -- co zwraca funkcja (tutaj typ int)
AS
BEGIN
-- ciało - czyli to co ma się wydarzyć
Return 0; -- tutaj funkcja zawsze zwróci 0.
End

Jeśli już mamy zdefiniowaną funkcję o takiej nazwie, możemy wtedy słowo create zastąpić alter, Spowoduje to zastąpienie funkcji nowszą definicją.

Funkcje z założenia przyjmują argumenty i zwracają wartości. Weźmy pod uwagę funkcję cosinus(cos(x))
przyjmuje parametr x- liczbę rzeczywistą, zwraca wartość również w postaci liczby rzeczywistej. Podobnie jest w T-SQL.

CREATE FUNCTION [dbo].moja_suma
(
@a int,
@b int

)
RETURNS int
AS
BEGIN
Return @a+@b;
End

By skorzystać z tak zdefiniowanej funkcji należy:

select dbo.moja_suma(1,2);

Oczywiście możemy tej funkcji użyć w dowolnej operacji DML.
W funkcjach możemy do zmiennej pobierać wartości z innych tabel.