W poprzedniej części przygotowaliśmy szkic procedury składowanej implementującej prosty mechanizm wykrywania danych osobowych.
To czego zabrakło, to wykorzystanie funkcji tabelarycznych do wykrywania tych danych, które posiadają sumę kontrolną i same wyrażenia regularne mogą nie wystarczyć.
Zajmijmy się identyfikatorami PESEL, NIP, REGON. Zainteresowanych budową takich identyfikatorów odsyłam do powszechnie dostępnych źródeł (m.in WIkipedia).
Poniżej przedstawiono kod trzech prostych funkcji tabelarycznych napisanych w Transact SQL.
PESEL
CREATE FUNCTION [dbo].[fnCheckPESEL] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN
WITH CheckBase as
(
SELECT
RTRIM(LTRIM(@value))
AS RAWPESEL,
LEFT(@value,10) +
TRY_CONVERT(VARCHAR(1), ((
TRY_CONVERT(INT,SUBSTRING(@value,1,1))*9 +
TRY_CONVERT(INT,SUBSTRING(@value,2,1))*7 +
TRY_CONVERT(INT,SUBSTRING(@value,3,1))*3 +
TRY_CONVERT(INT,SUBSTRING(@value,4,1))*1 +
TRY_CONVERT(INT,SUBSTRING(@value,5,1))*9 +
TRY_CONVERT(INT,SUBSTRING(@value,6,1))*7 +
TRY_CONVERT(INT,SUBSTRING(@value,7,1))*3 +
TRY_CONVERT(INT,SUBSTRING(@value,8,1))*1 +
TRY_CONVERT(INT,SUBSTRING(@value,9,1))*9 +
TRY_CONVERT(INT,SUBSTRING(@value,10,1))*7)% 10))
as PESEL
)
select RAWPESEL, PESEL,
isValid = case when RAWPESEL = PESEL
and RAWPESEL like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
then
1
else
0
end
from CheckBase
NIP
CREATE FUNCTION [dbo].[fnCheckNIP] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN
WITH CheckBase as
(
SELECT
RTRIM(LTRIM(@value))
AS RAWNIP,
LEFT(@value,9) +
TRY_CONVERT(VARCHAR(1), ((
TRY_CONVERT(INT,SUBSTRING(@value,1,1))*6
+ TRY_CONVERT(INT,SUBSTRING(@value,2,1))*5
+ TRY_CONVERT(INT,SUBSTRING(@value,3,1))*7
+ TRY_CONVERT(INT,SUBSTRING(@value,4,1))*2
+ TRY_CONVERT(INT,SUBSTRING(@value,5,1))*3
+ TRY_CONVERT(INT,SUBSTRING(@value,6,1))*4
+ TRY_CONVERT(INT,SUBSTRING(@value,7,1))*5
+ TRY_CONVERT(INT,SUBSTRING(@value,8,1))*6
+ TRY_CONVERT(INT,SUBSTRING(@value,9,1))*7)% 11))
as NIP
)
select RAWNIP, NIP,
isValid = case when RAWNIP = NIP
and RAWNIP like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
then
1
else
0
end
from CheckBase
REGON
ALTER FUNCTION [dbo].[fnCheckREGON] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN
WITH CheckBase as
(
SELECT
RTRIM(LTRIM(@value))
AS RAWREGON,
LEFT(@value,8) +
REPLACE(
TRY_CONVERT(VARCHAR(1),
(
(
TRY_CONVERT(INT,SUBSTRING(@value,1,1))*8
+TRY_CONVERT(INT,SUBSTRING(@value,2,1))*9
+TRY_CONVERT(INT,SUBSTRING(@value,3,1))*2
+TRY_CONVERT(INT,SUBSTRING(@value,4,1))*3
+TRY_CONVERT(INT,SUBSTRING(@value,5,1))*4
+TRY_CONVERT(INT,SUBSTRING(@value,6,1))*5
+TRY_CONVERT(INT,SUBSTRING(@value,7,1))*6
+TRY_CONVERT(INT,SUBSTRING(@value,8,1))*7
) % 11)),'*','0')
as REGON
)
select RAWREGON, REGON,
isValid = case when RAWREGON = REGON
and RAWREGON like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
then
1
else
0
end
from CheckBase
Przykłady wywołania
select * from [dbo].[fnCheckPESEL] ('00000000001')
select * from [dbo].[fnCheckREGON] ('000000001')
select * from [dbo].[fnCheckNIP] ('0000000001')
Wynik działania
RAWPESEL PESEL isValid
00000000001 00000000000 0
RAWREGON REGON isValid
000000001 000000000 0
RAWNIP NIP isValid
0000000001 0000000000 0
Każda z tych funkcji jest zbudowana na podstawie tego samego schematu.
Na poczatku pobieramy na wejściu danę do weryfikacji, następnie pobieramy cześć danych bez sumy kontrolnej i wyliczany na jej podstawie brakującą sumę kontrolną. Jeśli tak spreparowany identyfikator jest tożsami z wejściowym i dodatkowo składa się z samych cyfr, możemy uznać, ze mamy do czynienia z poprawnymi danymi. W ten sposón wyznaczana jest kolumna isValid.
Jak można je zastosować ?
select 'PESEL detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckPESEL] (d.DataValue) as fn
where fn.isValid=1
select 'REGON detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckREGON] (d.DataValue) as fn
where fn.isValid=1
select 'NIP detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckNIP] (d.DataValue) as fn
where fn.isValid=1
Przydatne strony:
Dodaj komentarz