Estrarre Data e luogo di nascita dal codice fiscale

update Persone set DataNascita =

(

select '19' + SUBSTRING(codicefiscale,7,2) +

CASE

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'A' THEN '01'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'E' THEN '05'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'P' THEN '09'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'B' THEN '02'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'H' THEN '06'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'R' THEN '10'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'C' THEN '03'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'L' THEN '07'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'S' THEN '11'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'D' THEN '04'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'M' THEN '08'

WHEN cast (SUBSTRING(codicefiscale,9,1) as CHAR ) = 'T' THEN '12'

END +

case when LEN(

cast(CASE WHEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) < 35

THEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int)

ELSE (cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) -40) END as nvarchar(255))

) =1

then '0' +

cast(CASE WHEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) < 35

THEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int)

ELSE (cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) -40) END as nvarchar(255))

ELSE

cast(CASE WHEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) < 35

THEN cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int)

ELSE (cast(replace(replace(SUBSTRING (codicefiscale,10,2),'O','0') ,'I','1') as int) -40) END as nvarchar(255))

END

) where CodiceFiscale is not null

update Persone set LuogoNascita = (select UID from Comuni where Codice like SUBSTRING (codicefiscale,12,4) )