Spis treści:

Kategoria:SQL Server


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 intSUBSTRING(@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
SELECTFROM dbo.Pesel('67031211232')

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

BirthDateGenderValid
1967-03-12M1

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')

SELECTFROM PeselTable
CROSS APPLY dbo.Pesel(Pesel)

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

IDPeselBirthDateGenderValid
1670312112321967-03-12M1
26703D211232NULLNULL0
36703121123NULLNULL0
4883112401232088-11-12K0
555341198450NULLM0
6342803155652034-08-03K1

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

, 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.
Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !