Algoritmo T-SQL (devo usare SQL2000) per costruzione Codice Fiscale
Fulvio Canepari
non � che qualche anima pia mi potrebbe indicare se esiste l'algoritmo in
oggetto?
Ho fatto qualche ricerca ma al momento non sono riuscito a trovare nulla e,
sinceramente, non � che ho tanto voglia di farlo (f� caldo, non � per me,
...).
P.S.: Se mi mandate a stendere lo capisco :-)
Ciao
tiriamoavanti
news:9F69DF87-71B0-4C8C...@microsoft.com...
> Ciao a tutti,
> non � che qualche anima pia mi potrebbe indicare se esiste l'algoritmo in
> oggetto?
Non esiste tale algoritmo!
E' un errore comune credere che si possa generare il codice fiscale grazie a
semplici regole ed � anche in parte vero, ma l'impossibilit� di gestire i
duplicati in maniera corretta rende l'algoritmo di fatto inefficace.
Il codice fiscale per essere valido deve essere registrato (e pertanto
rilasciato) all'Anagrafe Tributaria.
Fulvio Canepari
Sono perfettamente a conoscenza che questo tipo di algoritmo non pu�
garantire l'univocit� del dato ma io lo uso comunque in VB6, VB.NET e C#.
Solo che non ho voglia di riscriverlo anche in T-SQL.
Grazie comunque di non avermi mandato a stendere
FC
"tiriamoavanti" <tiriam...@gmail.com> wrote in message
news:eExH78A8...@TK2MSFTNGP06.phx.gbl...
tiriamoavanti
> Un algoritmo � un algoritmo quindi esiste.
Cartesio non usava una ricorsione per�! :)
Se algoritmo = metodo di soluzione
Allora una soluzione sbagliata non pu� originare da un "algorimo"
propriamente detto.
Marcello
> Ciao a tutti,
> non � che qualche anima pia mi potrebbe indicare se esiste l'algoritmo
> in oggetto?
> Ho fatto qualche ricerca ma al momento non sono riuscito a trovare nulla
> e, sinceramente, non � che ho tanto voglia di farlo (f� caldo, non � per
Ciao,
ho scritto questo se ti pu� servire, si basa sulla definizione di cf di
wikipedia, per sql 2005 per�...
In caso di valori di ingresso inaccetabili ritorna null:
use tempdb
go
create function dbo.fi_GetCf(@Nome varchar(255), @Cognome varchar(255),
@Maschio bit, @DataNascita datetime, @CodiceCatastale char(4))
returns varchar(16) as begin
declare @Cf varchar(16)
set @Cf=''
/*
Cognome (3 lettere)
Vengono prese le consonanti del cognome (o dei cognomi, se ve ne � pi�
di uno) nel loro ordine: solo se sono insufficienti, si prelevano anche
le vocali, sempre nel loro ordine: comunque, le vocali vengono riportate
dopo le consonanti. Nel caso in cui un cognome abbia meno di tre
lettere, la parte di codice viene completata aggiungendo la lettera X
(es.: Fo -> FOX). Per le donne, viene preso in considerazione il solo
cognome da nubile.
*/
set @Cognome=replace(@Cognome, ' ', '')
if @Cognome like '%[^a-z]%' return null
if @Cognome='' return null;
with x as(
select L = left(@Cognome,1), R = stuff(@Cognome, 1, 1, ''), N=1
union all
select L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1
from x
where r>''
)
select top 3 @Cf = @Cf + L
from x
order by case when L in('a', 'e', 'i', 'o', 'u') then 1 else 0 end, N
set @Cf = left(@Cf + 'xx', 3);
/*
Nome (3 lettere)
Vengono prese le consonanti del nome (o dei nomi, se ve ne � pi� di
uno) in questo modo: se il nome contiene quattro o pi� consonanti, si
scelgono la prima, la terza e la quarta, altrimenti le prime tre in
ordine. Solo se il nome non ha consonanti a sufficienza, si prendono
anche le vocali: comunque, le vocali vengono riportate dopo le
consonanti. Nel caso in cui il nome abbia meno di tre lettere, la parte
di codice viene completata aggiungendo la lettera X. Un caso estremo si
incontra in alcuni soggetti provenienti dall'India nel passaporto dei
quali � riportata una sola parola al posto del cognome e del nome. Si
user� allora quella parola per generare le prime tre lettere del codice
e, non esistendo il nome, la seconda terzina di lettere del codice sar�
XXX.
*/
set @Nome=replace(@Nome, ' ', '')
if @Nome like '%[^a-z]%' return null;
with x as(
select L = left(@Nome,1), R = stuff(@Nome, 1, 1, ''), N=1
union all
select L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1
from x
where r>''
), y as(
select *,
Vocale = case when L in('a', 'e', 'i', 'o', 'u') then 1 else 0 end
from x
), z as(
select * , Cs=sum(1-Vocale) over(),
N2 = row_number() over(order by Vocale, N)
from y
)
select top 3 @Cf = @Cf + L
from z
where Cs<3 or N2!=2
order by N2
set @Cf = left(@Cf + 'xx', 6)
/*
Data di nascita e sesso (5 caratteri alfanumerici)
Anno di nascita (2 cifre): si prendono le ultime due cifre dell'anno di
nascita;
Mese di nascita (1 lettera): ad ogni mese dell'anno viene associata una
lettera in base a questa tabella:
Lettera Mese Lettera Mese Lettera Mese
A gennaio E maggio P settembre
B febbraio H giugno R ottobre
C marzo L luglio S novembre
D aprile M agosto T dicembre
Giorno di nascita e sesso (2 cifre): si prendono le due cifre del
giorno di nascita (se � compreso tra 1 e 9 si pone uno zero come prima
cifra); per i soggetti di sesso femminile a tale cifra va sommato il
numero 40.
*/
set @CF = @CF + right(convert(varchar,year(@DataNascita)),2) +
case month(@DataNascita) when 1 then 'A' when 2 then 'B' when 3 then
'C' when 4 then 'D' when 5 then 'E' when 6 then 'H' when 7 then 'L' when
8 then 'M' when 9 then 'P' when 10 then 'R' when 11 then 'S' when 12
then 'T' end +
right('0' + convert(varchar, day(@DataNascita) + case @Maschio when 1
then 0 else 40 end),2)
/*
Comune di nascita (4 caratteri alfanumerici)
Per questa parte di codice viene utilizzato il codice catastale del
comune di nascita, ossia una codifica predisposta dalla Direzione
Generale del Catasto, composta da una lettera e 3 cifre numeriche. Per i
nati al di fuori del territorio italiano si considera lo Stato estero di
nascita; in tal caso la sigla inizia con la lettera Z, seguita dal
numero identificativo della nazione.
*/
if @CodiceCatastale is null return null
if @CodiceCatastale not like '[a-z][0-9][0-9][0-9]' return null
set @CF = @CF + @CodiceCatastale;
/*
Codice di controllo (1 lettera)
A partire dai 15 caratteri alfanumerici ricavati in precedenza, si
determina il codice di controllo in base ad un particolare algoritmo,
che opera in questo modo: si mettono da una parte i caratteri
alfanumerici che si trovano in posizione dispari (il 1�, il 3� ecc.) e
da un'altra quelli che si trovano in posizione pari (il 2�, il 4� ecc.).
Fatto questo, i caratteri vengono convertiti in valori numerici
rispettando le seguenti tabelle:
<CUT>
*/
with x as(
select L = left(@CF,1), R = stuff(@CF, 1, 1, ''), N=1
union all
select L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1
from x
where r>''
), s as(
select k=sum(case N%2 when 1 then
case L when '0' then 1 when '9' then 21 when 'I' then 19 when
'R' then 8
when '1' then 0 when 'A' then 1 when 'J' then 21 when 'S'
then 12
when '2' then 5 when 'B' then 0 when 'K' then 2 when 'T'
then 14
when '3' then 7 when 'C' then 5 when 'L' then 4 when 'U'
then 16
when '4' then 9 when 'D' then 7 when 'M' then 18 when 'V'
then 10
when '5' then 13 when 'E' then 9 when 'N' then 20 when 'W'
then 22
when '6' then 15 when 'F' then 13 when 'O' then 11 when 'X'
then 25
when '7' then 17 when 'G' then 15 when 'P' then 3 when 'Y'
then 24
when '8' then 19 when 'H' then 17 when 'Q' then 6 when 'Z'
then 23 end
else
case L when '0' then 0 when '9' then 9 when 'I' then 8 when
'R' then 17
when '1' then 1 when 'A' then 0 when 'J' then 9 when 'S' then 18
when '2' then 2 when 'B' then 1 when 'K' then 10 when 'T'
then 19
when '3' then 3 when 'C' then 2 when 'L' then 11 when 'U'
then 20
when '4' then 4 when 'D' then 3 when 'M' then 12 when 'V'
then 21
when '5' then 5 when 'E' then 4 when 'N' then 13 when 'W'
then 22
when '6' then 6 when 'F' then 5 when 'O' then 14 when 'X'
then 23
when '7' then 7 when 'G' then 6 when 'P' then 15 when 'Y'
then 24
when '8' then 8 when 'H' then 7 when 'Q' then 16 when 'Z'
then 25 end
end)%26
from x
)
select @Cf = @Cf + char(97+k) from s
return upper(@CF)
end
go
select dbo.fi_GetCf('Marcello', 'Poletti', 1, '19730523', 'g160')
go
drop function fi_GetCf
> Ciao
marc.
Marcello
> set @Cf = left(@Cf + 'xx', 6)
Bug.
Per supportare il caso descritto di cognomi senza nomi, nella sezione
del Nome, bisogna sostituire la stringa qui sopra con:
set @Cf = left(@Cf + 'xxx', 6)
> marc.
rimarc.
Marcello
> where Cs<3 or N2!=2
Altro piccolo bug, questa �
where Cs<=3 or N2!=2
E segue una versione per sql 2000:
use tempdb
go
create function dbo.fi_GetCf(@Nome varchar(255), @Cognome varchar(255),
@Maschio bit, @DataNascita datetime, @CodiceCatastale char(4))
returns varchar(16) as begin
declare @Cf varchar(16)
set @Cf=''
/*
Cognome (3 lettere)
Vengono prese le consonanti del cognome (o dei cognomi, se ve ne � pi�
di uno) nel loro ordine: solo se sono insufficienti, si prelevano anche
le vocali, sempre nel loro ordine: comunque, le vocali vengono riportate
dopo le consonanti. Nel caso in cui un cognome abbia meno di tre
lettere, la parte di codice viene completata aggiungendo la lettera X
(es.: Fo -> FOX). Per le donne, viene preso in considerazione il solo
cognome da nubile.
*/
set @Cognome=replace(@Cognome, ' ', '')
if @Cognome like '%[^a-z]%' return null
if @Cognome='' return null;
declare @T table(L char(1) not null, R varchar(255) not null, N int
primary key, N2 int not null, Vocale as case when L in('a', 'e', 'i',
'o', 'u') then 1 else 0 end )
insert into @T
select L = left(@Cognome,1), R = stuff(@Cognome, 1, 1, ''), N=1, 0
while (select max(N) from @T) < len(@Cognome)
begin
insert into @T
select top 1 L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1, 0
from @T
order by N desc
if @@rowcount=0 break
end;
select top 3 @Cf = @Cf + L
from @T x
order by Vocale, N
set @Cf = left(@Cf + 'xx', 3);
/*
Nome (3 lettere)
Vengono prese le consonanti del nome (o dei nomi, se ve ne � pi� di
uno) in questo modo: se il nome contiene quattro o pi� consonanti, si
scelgono la prima, la terza e la quarta, altrimenti le prime tre in
ordine. Solo se il nome non ha consonanti a sufficienza, si prendono
anche le vocali: comunque, le vocali vengono riportate dopo le
consonanti. Nel caso in cui il nome abbia meno di tre lettere, la parte
di codice viene completata aggiungendo la lettera X. Un caso estremo si
incontra in alcuni soggetti provenienti dall'India nel passaporto dei
quali � riportata una sola parola al posto del cognome e del nome. Si
user� allora quella parola per generare le prime tre lettere del codice
e, non esistendo il nome, la seconda terzina di lettere del codice sar�
XXX.
*/
set @Nome=replace(@Nome, ' ', '')
if @Nome like '%[^a-z]%' return null;
delete @T;
if @Nome>''
begin
insert into @T
select L = left(@Nome,1), R = stuff(@Nome, 1, 1, ''), N=1, 0
while (select max(N) from @T) < len(@Nome)
begin
insert into @T
select top 1 L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1, 0
from @T
order by N desc
if @@rowcount=0 break
end;
end
declare @Cs int
select @Cs=count(*) from @T where Vocale=0
update @T
set N2=case t.Vocale when 0 then t.N-K else t.N-K + @Cs end
from @T t
inner join(
select t1.N, K=sum(isnull(t2.Vocale,0))
from @T t1
left outer join @T t2
on t2.N<t1.N
group by t1.N
) x
on x.N=t.N
select top 3 @Cf = @Cf + L
from @T
where @Cs<=3 or N2!=2
order by N2
set @Cf = left(@Cf + 'xxx', 6)
delete @T;
insert into @T
select L = left(@CF,1), R = stuff(@CF, 1, 1, ''), N=1, 0
while (select max(N) from @T) < len(@CF)
begin
insert into @T
select top 1 L = left(R,1), R = stuff(R, 1, 1, ''), N=N+1, 0
from @T
order by N desc
if @@rowcount=0 break
end;
select @Cf = @Cf + char(97+k)
from (
from @T x
)s
return upper(@CF)
end
go
select dbo.fi_GetCf('Marcello', 'Poletti', 1, '19730523', 'g160')
go
drop function fi_GetCf
> marc.
rimarc.
Fulvio Canepari
Grazie
FC
P.S.: ma con SQL2005 perch� hai utilizzato T-SQL e non il CLR?
Marcello
> E meno male che esisti se no avrebbero dovuto inventarti!
> Grazie
> FC
Ciao Fulvio, grazie a te.
> P.S.: ma con SQL2005 perch� hai utilizzato T-SQL e non il CLR?
Il clr va usato con parsimonia dove e solo dove sql non arriva oppure
arriva con grade dispendio di risorse.
Considera i seguenti casi:
- Inviare un sms
- Gestire le regular expressions
- Ridimensionare un'immagine
- Leggere un contenuto ftp
Si tratta di operazioni che non hanno a che fare con un database e
infatti non sono percorribili con strumenti standard tsql.
Oppure:
- Calcolare la distanza Levhenstein
- Eseguire lo smoothing dei dati
- Valutare una formula matematica
Queste operazioni non fanno ricorso a risorse esterne estranee al db
[file sistem, protocollo ftp, System.Drawing, Registro...] ma risultano
difficili da scrivere e poco performanti con un linguaggio set based e
quindi il clr pu� essere una risorsa interessante.
Nel caso che hai suggerito, il calcolo del cf, invece, il tsql � pi� che
sufficiente e anzi, pu� risultare migliore dei linguaggi strettamente
procedurali [per ottenere prima le consonanti e poi le vocali, in tsql
basta un order by!]
Inoltre il clr � molto pi� scomodo, ovviamente, degli stumenti nativi.
Va compilato, distribuito, abilitato...
Insomma, prima di usare il clr bisogna avere davvero escluso la
possibilit� di lavorare nativamente.
marc.