Qui n’a jamais remarqué le nombre de données littérales corrompues. par exemple dans une colonne censé comporter des noms de famille quelques uns commençant par un blanc ou d’autres contenant des chiffres ? La fonction proposée ici permet de rectifier à la volée (dans un déclencheur) les données incorrectement saisies…
Notre fonction prend en paramètre la donnée à contrôler et récrire, puis deux listes de caractères. La première liste, celle des caractères entrant contient tous les caractères susceptible d’être écrit lors de la saisie. Si un caractère saisi n’est pas dans cette liste il sera éliminé. La seconde liste contient les caractères de remplacement et doit avoir le même nombre de caractères que la première. Enfin, le dernier paramètre contient les caractères inclus dans la première liste, mais que l’on ne veut pas voir figurer en premier ou dernier caractère.
Par exemple pour un nom de famille on peut vouloir toutes les lettres de l’alphabet, mais aussi le tiret, l’apostrophe et le blanc. En revanche, il faut interdire le tiret, l’apostrophe et le blanc en premier ou dernier caractères. Si l’on veut que la saisie se fasse en caractères majuscules, alors il faut faire la correspondance entre les deux listes sans oublier les accents sur les majuscules (c’est une obligation légale…).
Voyons le code de cette fonction :
CREATE FUNCTION dbo.F_RESCRIT
(@DATA VARCHAR(max), -- la donnée à récrire
@CHARSIN VARCHAR(256), -- les caractères autorisés en entrée (si blanc ne doit être situé ni en premier ni en dernier)
@CHARSOUT VARCHAR(256), -- les caractères de remplacement en sortie (si blanc ne doit être situé ni en premier ni en dernier)
@EXCEPT_EXTREM VARCHAR(128)) -- les caractères interdit en début et fin de donnée (si blanc ne doit être situé ni en premier ni en dernier)
RETURNS VARCHAR(max)
/******************************************************************************
* Fonction de correction d'écriture à implanter dans un trigger *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-03-20 *
******************************************************************************/
AS
BEGIN
-- variables locales
DECLARE @I INT, @POS INT, @C CHAR(1), @DATAOUT VARCHAR(max);
-- contrôle des effets de bord
IF @DATA IS NULL OR @CHARSIN IS NULL OR @CHARSOUT IS NULL RETURN NULL;
IF @DATA = '' OR @CHARSIN = '' OR @CHARSOUT = '' RETURN '';
IF LEN(@CHARSIN) <> LEN(@CHARSOUT) RETURN @DATA;
-- préparation des variables
SELECT @I = 1, @DATAOUT = '';
-- boucle de traitement des caractères de début :
WHILE CHARINDEX(SUBSTRING(@DATA, @I, 1), @EXCEPT_EXTREM COLLATE Latin1_General_BIN2) > 0
SET @I += 1;
-- boucle de traitement des caractères de fin :
WHILE CHARINDEX(SUBSTRING(@DATA, LEN(@DATA), 1), @EXCEPT_EXTREM COLLATE Latin1_General_BIN2) > 0
SET @DATA = SUBSTRING(@DATA, 1, LEN(@DATA) - 1);
-- boucle de traitement des caractères internes
WHILE @I <= LEN(@DATA)
BEGIN
-- extraction du caractère de position I
SET @C = SUBSTRING(@DATA, @I, 1);
-- extraction de la position du caractère dans la liste d'entrée
SET @POS = CHARINDEX(@C, @CHARSIN COLLATE Latin1_General_BIN2);
-- si ce caractère est présent dans la liste d'entrée
IF @POS > 0
BEGIN
-- si c'est une extrémité
IF @I = 1 OR @I = LEN(@DATA)
BEGIN
-- alors regarder s'il figure dans la liste des exceptions
IF CHARINDEX(@C, @EXCEPT_EXTREM COLLATE Latin1_General_BIN2) > 0
BEGIN
-- si c'est le cas, reboucler immédiatement
SET @I = @I + 1;
CONTINUE;
END;
END;
-- sinon, trouver le caractères correspondant dans la liste de sortie et l'affecter par concaténation au résultat
SET @DATAOUT = @DATAOUT + SUBSTRING(@CHARSOUT, @POS, 1)
END
-- incrémenter la boucle
SET @I = @I + 1;
END;
-- renvoyer le résultat
RETURN @DATAOUT;
END
GO
Exemples divers :
1) Contrôle et rectification pour un nom (pouvant avoir des accents, apostrophe, tiret et blanc) :
SELECT dbo.F_RESCRIT (' Lætitia Desmalières d''Artigny-Lavernes',
'-'' àáâãäæçèéêëìíîïñòóôõöœùúûüýœabcçdefghijklmnopqrstuvwxyzÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝŒABCDEFGHIJKLMNOPQRSTUVWXYZ',
'-'' ÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖŒÙÚÛÜÝŒABCCDEFGHIJKLMNOPQRSTUVWXYZÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝŒABCDEFGHIJKLMNOPQRSTUVWXYZ',
'- ''') AS RESCRIT
RESCRIT
---------------------------------------
LÆTITIA DESMALIÈRES D'ARTIGNY-LAVERNES
2) Contrôle et rectification pour un code de référence (pouvant avoir tiret, point, blanc, blanc souligné transformé en blanc souligné, et chiffres et lettres corrigées en majuscules désaccentués) :
SELECT dbo.F_RESCRIT ('_SXR''923-48xé',
'- _.àáâãäçèéêëìíîïñòóôõöùúûüýabcçdefghijklmnopqrstuvwxyzÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'___ AAAAACEEEEIIIINOOOOOUUUUYABCCDEFGHIJKLMNOPQRSTUVWXYZAAAAACEEEEIIIINOOOOOUUUUYABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'- _.') AS RESCRIT
RESCRIT
---------------------------------------
SXR923_48XE
3) contrôle et rectification pour un Libellé litteral fort (pouvant avoir tiret, chiffres et point et blanc souligné) :
SELECT dbo.F_RESCRIT (' St. Péray Lès Avignon',
'- ''.àáâãäæçèéêëìíîïñòóôõöœùúûüýabcçdefghijklmnopqrstuvwxyzÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝŒABCDEFGHIJKLMNOPQRSTUVWXYZ',
'- ''.ÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖŒÙÚÛÜÝABCCDEFGHIJKLMNOPQRSTUVWXYZÀÁÂÃÄÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝŒABCDEFGHIJKLMNOPQRSTUVWXYZ',
'- ''.') AS RESCRIT
RESCRIT
-----------------------
ST. PÉRAY LÈS AVIGNON
Exemple d’implantation dans une table pour le contrôle d’une colonne :
CREATE TRIGGER E_IU_PRM
ON S_ADM.T_S_PARAMETRE_PRM
FOR INSERT, UPDATE
AS
UPDATE S_ADM.T_S_PARAMETRE_PRM
SET PRM_CODE = dbo.F_RESCRIT (PRM_CODE,
'- _.àáâãäçèéêëìíîïñòóôõöùúûüýabcçdefghijklmnopqrstuvwxyzÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'___.AAAAACEEEEIIIINOOOOOUUUUYABCCDEFGHIJKLMNOPQRSTUVWXYZAAAAACEEEEIIIINOOOOOUUUUYABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'- _')
WHERE PRM_ID IN (SELECT PRM_ID FROM inserted);
GO
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
