Vérifiez la validité d’un numéro de sécurité sociale avec SQL Server.

Lorsqu’on est en France et que l’on travaille dans les logiciels de gestion pour le domaine de la santé, les questions de comment remonter tous les numéros de sécurité sociale « non valide » d’une table d’identifiants ou comment vérifier la validité d’un NIRPP dans un trigger arrivent assez rapidement.

Or, le contrôle d’un NIR n’est pas si facile lorsque l’on veut couvrir un maximum de cas (numéro temporaire pour les étrangers, département d’outre-mer, corse, calcul de clé, …) .

Pour ceux qui veulent éviter de perde plusieurs heures (voire plusieurs jours) sur la meilleure solution à adopter pour ce problème très franco-français, je vais essayer de vous synthétiser ma solution pour que vous puissiez la réutiliser dans votre base de données grâce à une bonne vielle requête SQL.

Tous d’abord, nous allons devoir référencer une assembly dans SQL Server pour nous permettre d’accéder à des fonctionnalités de RegEx.

Ces fonctionnalités ne sont pas fournies en natif par Microsoft pour la simple et bonne raison que celui-ci conseille de passer par du code C# pour des questions de simplicité et de performances.

Pour éviter de vous prendre la tête, exécutez au préalable le script SQL suivant.

Une fois ceci fait, vous allez pouvoir facilement contrôler la validité d’un numéro de sécurité sociale via une simple ligne de commande SQL !

Par exemple, pour obtenir tous les NIRPP non valides de la table « Identifiant » dont le numéro est dans le champ « Valeur », exécutez la requête suivante :

select * from Identifiant where (dbo.RegexMatch(Valeur, '^([1-37-8])([0-9]{2})(0[0-9]|[2-35-9][0-9]|[14][0-2])((0[1-9]|[1-8][0-9]|9[0-69]|2[abAB])(00[1-9]|0[1-9][0-9]|[1-8][0-9]{2}|9[0-8][0-9]|990)|(9[78][0-9])(0[1-9]|[1-8][0-9]|90))([0-9]{3})([0-8][0-9]|9[0-7])',1) = 0
OR 
 CAST(SUBSTRING(Valeur, 14, 2) AS int) <> CASE 
 WHEN valeur like '%A%' THEN (97 - ((CAST(SUBSTRING(REPLACE(Valeur,'A','0'), 1, 13) as bigint) - 1000000) % 97)) 
 WHEN valeur like '%B%' THEN (97 - ((CAST(SUBSTRING(REPLACE(Valeur,'B','0'), 1, 13) as bigint) - 2000000) % 97)) 
 ELSE (97 - (CAST(SUBSTRING(Valeur, 1, 13) as bigint) % 97)) 
 END ) 

Pour obtenir les NIRPP valides, il suffit de replacer le 0 par 1, le OR par un AND et le <> par un = ce qui donne :

select * from Identifiant where 
(dbo.RegexMatch(Valeur, '^([1-37-8])([0-9]{2})(0[0-9]|[2-35-9][0-9]|[14][0-2])((0[1-9]|[1-8][0-9]|9[0-69]|2[abAB])(00[1-9]|0[1-9][0-9]|[1-8][0-9]{2}|9[0-8][0-9]|990)|(9[78][0-9])(0[1-9]|[1-8][0-9]|90))([0-9]{3})([0-8][0-9]|9[0-7])',1) = 1
AND 
 CAST(SUBSTRING(Valeur, 14, 2) AS int) = CASE 
 WHEN valeur like '%A%' THEN (97 - ((CAST(SUBSTRING(REPLACE(Valeur,'A','0'), 1, 13) as bigint) - 1000000) % 97)) 
 WHEN valeur like '%B%' THEN (97 - ((CAST(SUBSTRING(REPLACE(Valeur,'B','0'), 1, 13) as bigint) - 2000000) % 97)) 
 ELSE (97 - (CAST(SUBSTRING(Valeur, 1, 13) as bigint) % 97)) 
 END ) 

Et voilà ! Avec peu d’effort, vous allez pouvoir faire des requêtes pour nettoyer vos bases de données, puis faire des Check Constraint et autres triggers pour que tout le monde arrête de pourrir vos tables avec des numéros de sécu invalides !

Alors, merci qui ? :p