Pobieranie daty urodzenia i płci z numeru PESEL - SQL Server

Jakiś czas temu pokazałem, jak wyciągnąć pewne informacje z numeru PESEL w Javascript (Pobieranie daty urodzenia i płci z numeru PESEL - Javascript). Tym razem czas na SQL Server. Numery PESEL w bazach danych przechowywane są zwykle w postaci łańcuchów znaków. Pokażę, w jaki sposób pobrać z takiego łańcucha datę urodzenia, płeć i znacznik określający prawidłowość sumy kontrolnej. Nie będę przedstawiał teorii, bo została już ona wcześniej opisana we wspomnianym artykule o Javascript. Zainteresowanych lub tych, którzy nie rozumieją dziwnego przeliczania miesięcy i lat, sumy kontrolnej i kilku innych ciekawostek odsyłam tam. Tym razem będzie prawie czysty skrypt.

Funkcja Pesel - pobieranie daty urodzenia, płci i wartości kontrolnej

Jak napisałem, tak zrobię. W skrypcie umieściłem kilka komentarzy, więc myślę, że nie powinno być problemu ze zrozumieniem (po warunkiem zapoznania się z teorią). Skrypt SQL tworzący funkcję pokazany jest poniżej:

CREATE FUNCTION dbo.Pesel (@pesel nvarchar(11))
RETURNS @ret TABLE
(
  BirthDate date,
  Gender char,
  Valid int
)
AS
BEGIN
  --Współczynniki sumy kontrolnej
  DECLARE @fact varchar(11)='13791379131'
  --Suma kontrolna
  DECLARE @sum int=0
  DECLARE @i int=1
  --Czy numer PESEL jest prawidłowy
  DECLARE @valid int=1
  --Czy w numerze są same cyfry
  DECLARE @validInt int=1
  DECLARE @c char
  DECLARE @date date
  DECLARE @gender char

  --Sprawdź długość numeru PESEL
  IF (LEN(@pesel)!=11)
    SET @validInt=0
  ELSE
    WHILE @i<=11
    BEGIN
      --Sprawdź, czy wszystkie znaki to cyfry
      --Jeżeli tak, policz sumę kontrolną
      SET @c=SUBSTRING(@pesel,@i,1)
      IF (@c<'0' OR @c>'9')
        SET @validInt=0
      ELSE
        SET @sum+=CAST(SUBSTRING(@fact,@i,1) as int)*@c
      SET @i+=1
    END

  IF @sum%10!=0 OR @validInt=0
    SET @valid=0

  IF @validInt=1
  BEGIN
    DECLARE @int int = SUBSTRING(@pesel,1,2)
    DECLARE @rok int = 1900+@int;
    --Przeanalizuj pierwszą cyfrę miesiąca, może tam być
    --informacja o latach 1800-1899 i 2000-2299
    SET @int = SUBSTRING(@pesel,3,1)
    IF (@int>=2 AND @int<8)
      SET @rok+=@int/2*100;
    IF (@int>=8)
      SET @rok-=100;

    DECLARE @miesiac int = (@int%2)*10+SUBSTRING(@pesel,4,1);

    DECLARE @str varchar(10) = CAST(@rok AS varchar)+
      CASE WHEN @miesiac<10 THEN '0' ELSE '' END+
      CAST(@miesiac AS varchar)+SUBSTRING(@pesel,5,2);
    IF ISDATE(@str)=1
      SET @date=CAST(@str as date)
    ELSE
      SET @valid=0
    --Płeć zapisana jest na 10 znaku
    SET @gender = CASE WHEN SUBSTRING(@pesel,10,1)%2=1 THEN 'M' ELSE 'K' END
  END

  INSERT @ret SELECT @date, @gender, @valid
  RETURN
END

Sposób wykorzystania funkcji

Funkcja zwraca tablicę, więc najprostszym sposobem jej wywołania będzie taki oto skrypt:

--Test funkcji
SELECT * FROM dbo.Pesel('67031211232')

Wynikiem powyższej instrukcji będzie następująca tabela:

BirthDate	Gender	Valid
1967-03-12	M	1

Otrzymujemy trzy kolumny: datę urodzenia, płeć (K-Kobieta, M-Mężczyzna) oraz znacznik określający poprawność sumy kontrolnej (1-poprawna suma kontrolna, 0-błędna).

Wspomniałem na początku, że najczęściej w bazie te numery już są zapisane. Jak dołożyć do wyników zapytania informacje z funkcji? Najprościej użyć instrukcji CROSS APPLY. Pokazano to na poniższym listingu:

--Tabela testowa
CREATE TABLE PeselTable
(
ID int identity(1,1),
Pesel varchar(11)
)

--Wstawiamy kilka numerów do testowania
INSERT INTO PeselTable VALUES ('67031211232'), ('6703D211232'),
('6703121123'), ('88311240123'), ('55341198450'), ('34280315565')

SELECT * FROM PeselTable
CROSS APPLY dbo.Pesel(Pesel)

Tym razem wynikiem działania instrukcji będzie nieco większa tabela. Przyjrzyjmy się zatem rezultatom:

ID	Pesel	BirthDate	Gender	Valid
1	67031211232	1967-03-12	M	1
2	6703D211232	NULL	NULL	0
3	6703121123	NULL	NULL	0
4	88311240123	2088-11-12	K	0
5	55341198450	NULL	M	0
6	34280315565	2034-08-03	K	1

Funkcja napisana jest tak, aby mimo wszystko wyciągać informacje, jeżeli tylko pesel składa się z samych cyfr i ma odpowiednią długość. Ci, którzy przeczytali teorię wiedzą, że w Polsce istnieją nieprawidłowe numery i trzeba sobie jakoś z nimi radzić. Ta funkcja w przypadkach kontrowersyjnych zwraca NULL.

Kategoria:SQL Server

Paweł Dyl, 2013-12-20

Komentarze:

A.Slawek (2017-11-27 11:38:30)

Coś tu się chyba rok nie tak liczy ?
czy fragment :
IF (@int>=2 AND @int<8)
SET @rok+=@int/2*100;
nie powinien wyglądac tak :
IF (@int>=2 AND @int<8)
SET @rok+=100;

?

PD (2017-11-28 08:52:18)

Wydaje się, że wszystko jest dobrze. Dla wartości 2 i 3 powinno być dodane 100 (w arytmetyce liczb całkowitoliczbowych 2/2=1 i 3/2=1), dla 4 i 5 powinno być dodane 200 (4/2=2, 5/2=2). Bardziej szczegółowy opis znajduje się w artykule o JavaScript wskazanym we wstępie.

Spis treści:

Pobieranie daty urodzenia i płci z numeru PESEL - SQL Server

Funkcja Pesel - pobieranie daty urodzenia, płci i wartości kontrolnej

Sposób wykorzystania funkcji

Komentarze: