Validité SIRET en SQL

Version imprimable

Juste pour partager le résultat de mon casse-tête

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
 
CREATE DEFINER = 'root'@'%'
FUNCTION EstSiretValide(Siret varchar(255))
  RETURNS tinyint(1)
BEGIN
  DECLARE somme bigint;
  DECLARE tmp bigint;
  DECLARE LongueurSiret int;
  DECLARE I int;
  DECLARE S varchar(255);
  DECLARE C char;
 
  SET S = TRIM(Siret);
  SET somme = 0;
 
  -- Longueur 14 obligatoirement et QUE des chiffres
  IF S REGEXP '^[0-9]{14}$' = FALSE THEN  
    RETURN FALSE;
  END IF;
 
  SET I = 1;
  Loop_Label: LOOP
 
    IF (I % 2) = 1 THEN
 
      SET C = SUBSTRING(S, I, 1);      
      SET tmp = CAST(C AS UNSIGNED) * 2;
 
      IF tmp > 9 THEN
        SET tmp = tmp - 9;
      END IF;
 
    ELSE 
      SET tmp = CAST( SUBSTRING(S, I, 1) AS UNSIGNED);
    END IF;
 
    SET somme = somme + tmp;
 
    IF I = 14 THEN
      LEAVE `Loop_Label`;
    END IF;
 
    SET I = I + 1;
  END LOOP;
 
  IF (somme % 10) = 0 THEN
    RETURN TRUE;
  END IF;
 
  RETURN FALSE;
 
END

05/03/2020, 13h01
CinePhil

Ce serait bien d'ajouter des commentaires pour expliquer ce qui est contrôlé, au delà de la longueur de 14 digits.

Ce serait même encore mieux d'en faire un billet de blog, voire un article.
05/03/2020, 13h09
Papy214

Pour ce qui est de rajouter des commentaires, je ferai ça quand j'aurai un peu de temps en dehors du boulot.
05/03/2020, 14h35
CinePhil

OK merci.
Ça pourrait me servir un jour.
05/03/2020, 14h46
SQLpro

Votre fonction est très mal codée et incomplète....
1) pourquoi un VARCHAR(255) pour une information de 14 caractères ? Vous risquez, même avec votre expression régulière de rejeter des données valides !
Exemple, la chaine :
'12345678945612 ' est un SIRET valide en SQL, car les blancs ne sont pas significatif à la fin d'une chaine.
Donc, on met un CHAR(14) comme type en entrée !

2) les SIRET de la Poste ne sont pas conforme à la clef de LUHN. Lors du contrôle, il faut appliquer la vérification classique, puis si règle classique non vérifiée, vérifier alors que la somme simple des chiffres du SIRET est un multiple de cinq.

Exemple : 35600000000048 est un n° valide
Autre exemple : 356 000 539 14285

A +
05/03/2020, 16h11
Papy214

Le code n'est surement pas écrit dans les règles de l'art et ça demande très certainement des améliorations.

En fait, je me suis limité à ça parce que j'avais besoin de valider certaines données lors d'un import de données clients.
C'est pour ça par exemple que le paramètre est un varchar(255) parce que les données fournies par le client peuvent être complètement farfelues.

Pour les blancs entre les éléments je n'ai pas prévu ça c'est vrai. Je rajouterai la suppression des espaces avant traitement.
Comme pour la somme des chiffres qui doit être un multiple de 5, je ne savais pas non plus.
Merci pour ces 2 précisions.

Salut à tous.

Je me suis inspiré de cette page :
--> https://blog.pagesd.info/2012/09/05/...o-siret-poste/
ainsi que du wikipedia pour le calcul de 'luhn'.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP FUNCTION IF EXISTS `Siren`
--------------
 
--------------
CREATE FUNCTION `Siren`(_Siren char(9))
RETURNS BOOLEAN
BEGIN
        DECLARE _v    CHAR(10)  DEFAULT '0246813579';
        DECLARE _i    SMALLINT  DEFAULT NULL;
        DECLARE _som  SMALLINT  DEFAULT NULL;
 
        SET _som = 0;
        SET _i   = 9;
 
        WHILE _i > 0 DO
                IF _i%2 = 0 THEN
                        SET _som = _som + cast(substring(_v,substring(_Siren,_i,1)+1,1) as unsigned);
                ELSE    SET _som = _som + cast(             substring(_Siren,_i,1)      as unsigned);
                END IF;
 
                SET _i = _i - 1;
        END WHILE;
 
        return (_som%10 = 0);
END
--------------
 
--------------
DROP FUNCTION IF EXISTS `Siret`
--------------
 
--------------
CREATE FUNCTION `Siret`(_Siret char(14))
RETURNS BOOLEAN
BEGIN
        DECLARE _v    CHAR(10)  DEFAULT '0246813579';
        DECLARE _i    SMALLINT  DEFAULT NULL;
        DECLARE _som  SMALLINT  DEFAULT NULL;
 
        DECLARE abort CONDITION  FOR SQLSTATE '45000';
        DECLARE EXIT  HANDLER    FOR abort BEGIN RESIGNAL; END;
 
        IF length(_Siret) != 14 THEN
                signal abort set MESSAGE_TEXT = 'Longueur du Siret différent de 14 !', MYSQL_ERRNO = 1;
         END IF;
 
        IF not regexp_like(_Siret,'^[[:digit:]]+$') THEN
                signal abort set MESSAGE_TEXT = 'Siret non Numérique !', MYSQL_ERRNO = 2;
        END IF;
 
        IF `siren` (substring(_Siret,1,9)) = 0 THEN
                signal abort set MESSAGE_TEXT = 'Siren non Conforme !', MYSQL_ERRNO = 3;
        END IF;
 
        IF _Siret = '35600000000048' THEN
                return true;
        END IF;
 
        SET _som = 0;
        SET _i   = 14;
 
        WHILE _i > 0 DO
                IF substring(_Siret,1,9) = '356000000' THEN
                        SET _som = _som + cast(             substring(_Siret,_i,1)      as unsigned);
                ELSEIF _i%2 = 1 THEN
                        SET _som = _som + cast(substring(_v,substring(_Siret,_i,1)+1,1) as unsigned);
                ELSE    SET _som = _som + cast(             substring(_Siret,_i,1)      as unsigned);
                END IF;
 
                SET _i = _i - 1;
        END WHILE;
 
        if _som%10 = 0 THEN
                return (_som%10 = 0);
        ELSEIF substring(_Siret,1,9) = '356000000' THEN
                return (_som%5 = 0);
        ELSE    return (_som%10 = 0);
        END IF;
END
--------------
 
--------------
COMMIT
--------------
 
--------------
select `siret`('732829320     ')
--------------
 
ERROR 1 (45000) at line 107: Longueur du Siret différent de 14 !
--------------
select `siret`('732829320X0074')
--------------
 
ERROR 2 (45000) at line 108: Siret non Numérique !
--------------
select `siret`('73282932100074')
--------------
 
ERROR 3 (45000) at line 109: Siren non Conforme !
--------------
select `siret`('732829320000742')
--------------
 
ERROR 1406 (22001) at line 110: Data too long for column '_Siret' at row 1
--------------
select `siret`('73282932000074')
--------------
 
+---------------------------+
| `siret`('73282932000074') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
select `siret`('42226020800026')
--------------
 
+---------------------------+
| `siret`('42226020800026') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
select `siren`('356000000')
--------------
 
+----------------------+
| `siren`('356000000') |
+----------------------+
|                    1 |
+----------------------+
--------------
select `siret`('35600000000048')
--------------
 
+---------------------------+
| `siret`('35600000000048') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
select `siret`('35600000066180')
--------------
 
+---------------------------+
| `siret`('35600000066180') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
select `siret`('35600000049837')
--------------
 
+---------------------------+
| `siret`('35600000049837') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
select `siret`('35600053914285')
--------------
 
+---------------------------+
| `siret`('35600053914285') |
+---------------------------+
|                         1 |
+---------------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

06/03/2020, 09h44
Papy214

euh ... comment dire ? whaouh ? :oops:

évidemment, ça n'a rien à voir avec mon code très pauvre et mal écrit.
Mais comme je le dis dans un autre fil, je ne fais ça que de temps à autre, mon vrai boulot c'est développeur Delphi :D
Rien à voir avec les spécialistes que vous êtes

Donc, je ne peux que dire merci et chapeau bas !:)