août
2011
Un article de zinzineti
On peut être amener à travailler sur une « base de données » bizarre …mal conçue.
Une base de données dont la plupart des tables ne respecte même pas la première Forme Normale (1FN): les tables contiennent pas des valeurs atomiques ! Une image est souvent plus parlante que de long discours …. Voyons un extrait (trois colonnes) d’une de ces tables
Imaginer que vous devez livrer une application de statistiques de qualification des appels téléphoniques (Renseignement technique,Prix ou delais,Demande de devis,…)traités par chaque téléconseillers (AgentID) en fonction d’une période de date avec une « table » de ce type ! La première version des requêtes que j’ai écrites comportent des lignes kilométriques avec des fonctions de traitement des chaînes de caractères (REPLACE,SUBSTRING,CHARINDEX,LTRIM,RTRIM,….). Lors de la phase de recette de l’application le client décide d’ajouter de nouveau service avec une règle de nommage différente de l’existant ! Conséquence mes SUBSTRING,CHARINDEX,REPLACE, ne marchent plus avec les nouveaux services ajoutés. La solution que j’ai trouvée et qui marche à tous les coups c’est d’écrire une fonction SQL Split et l’utiliser conjointement avec CROSS APPLY dans les FROM de mes requêtes.
–=============================================================================================================================
–> Description : Split une chaine de caractères en fonction du séparateur fournit en entrée
–> Exemple d'utilisation :
– SELECT * FROM dbo.F_SQLSplit('Service:Relation clients:1-Qualification:Renseignement technique:1',':’)
–> Auteur : Etienne ZINZINDOHOUE
–=============================================================================================================================
CREATE FUNCTION [dbo].[F_SQLSplit] (@ChaineASpliter varchar(1024),@Seperateur char(1))
RETURNS @T_Split TABLE (IdSplit int ,ChaineSplit varchar(1024))
AS
BEGIN
DECLARE @i INT = 1
DECLARE @j INT
– Suppression des séparateurs inutiles se trouvant au début de la chaine à spliter
WHILE (SUBSTRING(@ChaineASpliter,1,1)) = @Seperateur
BEGIN
SET @ChaineASpliter = SUBSTRING(@ChaineASpliter,2,LEN(@ChaineASpliter)-1)
SET @i += 1
END
– Suppression des séparateurs inutiles se trouvant à la fin de la chaine à spliter
SET @j = LEN(@ChaineASpliter)
WHILE (SUBSTRING(@ChaineASpliter,@j,1)) = @Seperateur
BEGIN
SET @ChaineASpliter = SUBSTRING(@ChaineASpliter,1,@j -1)
SET @j -= 1
END
BEGIN
;WITH CTE_Split(Id, InitPosition, PositionSeparateur) AS (
SELECT 1, 1, CHARINDEX(@Seperateur, @ChaineASpliter)
UNION ALL
SELECT id + 1, PositionSeparateur + 1, CHARINDEX(@Seperateur, @ChaineASpliter, PositionSeparateur + 1)
FROM CTE_Split
WHERE PositionSeparateur > 0
)
INSERT @T_Split(IdSplit,ChaineSplit)
SELECT id,
SUBSTRING(@ChaineASpliter,InitPosition,
CASE
WHEN PositionSeparateur > 0 THEN PositionSeparateur-InitPosition
ELSE 1024
END) AS Split
FROM CTE_Split
RETURN;
END
END
GO
Une procédure stockée qui fait la même opération
– ==========================================================================================================
–> Description : Split une chaine de caractres en fonction du séparateur fournit en entrée
–> Exemple d'utilisation :
– EXEC P_SQLSplit N'Service:Relation clients:1-Qualification:Renseignement technique:1',N':'
–> Auteur : Etienne ZINZINDOHOUE
– ==========================================================================================================
ALTER PROCEDURE [dbo].[P_SQLSplit]
@ChaineASpliter VARCHAR(1024),@Seperateur CHAR(1)
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @i INT = 1
DECLARE @j INT
– Suppression des séparateurs inutiles se trouvant au début de la chaine à spliter
WHILE (SUBSTRING(@ChaineASpliter,1,1)) = @Seperateur
BEGIN
SET @ChaineASpliter = SUBSTRING(@ChaineASpliter,2,LEN(@ChaineASpliter)-1)
SET @i += 1
END
– Suppression des séparateurs inutiles se trouvant à la fin de la chaine à spliter
SET @j = LEN(@ChaineASpliter)
WHILE (SUBSTRING(@ChaineASpliter,@j,1)) = @Seperateur
BEGIN
SET @ChaineASpliter = SUBSTRING(@ChaineASpliter,1,@j -1)
SET @j -= 1
END
;WITH CTE_Split(IdSplit, InitPosition, PositionSeparateur) AS (
SELECT 1, 1, CHARINDEX(@Seperateur, @ChaineASpliter)
UNION ALL
SELECT IdSplit + 1, PositionSeparateur + 1, CHARINDEX(@Seperateur, @ChaineASpliter, PositionSeparateur + 1)
FROM CTE_Split
WHERE PositionSeparateur > 0
)
SELECT IdSplit,
SUBSTRING(@ChaineASpliter,InitPosition,
CASE
WHEN PositionSeparateur > 0 THEN PositionSeparateur-InitPosition
ELSE 1024
END) AS ChaineSplit
FROM CTE_Split
END TRY
BEGIN CATCH
SELECT ' Erreur dans la procedure [P_SQLSplit]'
END CATCH
—————————————
Etienne ZINZINDOHOUE
—————————————
Merci pour votre remarque. J’ai survolé rapidement le paragraphe 2.4 de votre article. Mais je pense qu’il faut que je lise posément tout l’article dans sa globalité [ car très riche pour être survolé
], histoire d’Updater mes connaissances sur le sujet…
A +
Bonsoir,
Pratique en effet. Mais selon vous, la table ne respecte pas la 1NF : c’est vrai dans l’esprit (modélisation à revoir), mais faux à la lettre, c’est-à-dire du point de vue du Modèle Relationnel de Données, car le domaine de référence de la colonne VALUE est CHAR(n) (ou VARCHAR(n), peu importe).
Je vous invite à lire le paragraphe 2.4 « L’esprit et la lettre » de l’article « Bases de données relationnelles et normalisation : de la première à la sixième forme normale » :
http://fsmrel.developpez.com/basesrelationnelles/normalisation/
Bien à vous,
François