La fonction ci dessous recherche toutes les occurrences d’une chaine dans une autre.
Elle fonctionne en renvoyant une table et permet la recherche de multiples occurrences d’un motif en tenant compte ou non de la casse, des accents….
La table retournée comporte le numéro ordinal de l’occurrence, sa position dans la chaîne, un extrait du texte avant, le motif tel que figurant dans le texte, le texte après, et le texte centré autour du motif cherché.
CREATE FUNCTION dbo.F_T_GET_OCCURS (@DATA VARCHAR(max), -- donnée à scruter
@PATTERN VARCHAR(32), -- motif à chercher
@WIDE SMALLINT, -- largeur du texte à restituer (si NULL, 80)
@CASE_SENSITIVE BIT, -- recherche sensible à la casse (si NULL insensible)
@ACCENT_SENSITIVE BIT) -- recherche sensible aux accents et autre caractères diacritiques (si NULL insensible)
RETURNS @T TABLE
(OCU_POS INT, -- position ordinale du motif dans la donnée (1er, 2e, 3e...)
OCU_PLACE INT, -- position du motif dans la chaîne de caractère
OCU_AVANT VARCHAR(256), -- texte avant le motif
OCU_EXTRAIT VARCHAR(32), -- texte du motif brut
OCU_APRES VARCHAR(256), -- texte après le motif
OCU_TEXTE CHAR(544)) -- assemblage du texte avant, avec et après
AS
/******************************************************************************
* Fonction de recherches de multi occurrences *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-10-02 *
*******************************************************************************
* cette fonction rechercher l'occurrence d'un motif (chaine de caratères) *
* dans une autre et renvoie une table avec la position des occurences les *
* quelques mots avants et après *
*******************************************************************************
* EXEMPLE : *
* SELECT * *
* FROM dbo.F_T_GET_OCCURS('Chercher une chaîne dans une autre chaîne', *
* 'ch', 20, 0, 0) *
* recherche toutes les occurrences de la chaine "ch" dans la phrase : *
* "Chercher une chaîne dans une autre chaîne" *
* sans tenir compte ni de la casse ni des accents *
* et donne : *
* OCU_POS OCU_PLACE OCU_AVANT OCU_EXTRAIT OCU_APRES OCU_TEXTE *
* ------- --------- ---------- ----------- ------------ -------------------- *
* 1 1 Â Â Â Â Â Â Â Â Â Â Ch ercher un Chercher un *
* 2 5 Â Â Â Â Â Â Cher ch er une ch Chercher une ch *
* 3 14  cher une ch aîne dans cher une chaîne dans *
* 4 36  ne autre ch aîne ne autre chaîne *
* *
******************************************************************************/
BEGIN
-- si conditions limite alors arrêt
IF @DATA IS NULL OR @PATTERN IS NULL RETURN;
-- paramétrage de départ
SELECT @WIDE = (COALESCE(@WIDE, 80) - LEN(@PATTERN)) / 2,
@CASE_SENSITIVE = COALESCE(@CASE_SENSITIVE, 0),
@ACCENT_SENSITIVE = COALESCE(@ACCENT_SENSITIVE, 0);
DECLARE @I INT, -- position du motif
@O INT, --
@N INT;
SELECT @O = 1, @N = 1;
-- première occurrence
SET @I = CASE
WHEN @ACCENT_SENSITIVE = 1 AND @CASE_SENSITIVE = 1 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CS_AS, @O)
WHEN @ACCENT_SENSITIVE = 1 AND @CASE_SENSITIVE = 0 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CS_AI, @O)
WHEN @ACCENT_SENSITIVE = 0 AND @CASE_SENSITIVE = 1 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CI_AS, @O)
ELSE CHARINDEX(@PATTERN, @DATA COLLATE French_CI_AI, @O)
END;
-- tant qu'une occurrence est trouvée
WHILE @I > 0
BEGIN
-- insérer les éléments trouvés
INSERT INTO @T (OCU_POS, OCU_PLACE, OCU_AVANT, OCU_EXTRAIT, OCU_APRES)
SELECT @N, @I,
REVERSE(CASE -- partie avant
WHEN @I -1 < @WIDE -- le début est plus court que la demi largeur
THEN SUBSTRING(@DATA, 1, @I - 1)
ELSE SUBSTRING(@DATA, @I - @WIDE, @WIDE)
END),
SUBSTRING(@DATA, @I, LEN(@PATTERN)), -- motif
CASE -- partie après
WHEN LEN(@DATA) - @I - LEN(@PATTERN) + 1 < @WIDE -- la fin est plus courte que la demi largeur
THEN SUBSTRING(@DATA, @I + LEN(@PATTERN), LEN(@DATA) - @I - LEN(@PATTERN) + 1)
ELSE SUBSTRING(@DATA, @I + LEN(@PATTERN), @WIDE)
END;
-- avancer dans le texte
SELECT @N = @N + 1, @O = @I + LEN(@PATTERN) - 1;
-- chercher l'occurrence suivante
SET @I = CASE
WHEN @ACCENT_SENSITIVE = 1 AND @CASE_SENSITIVE = 1 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CS_AS, @O)
WHEN @ACCENT_SENSITIVE = 1 AND @CASE_SENSITIVE = 0 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CS_AI, @O)
WHEN @ACCENT_SENSITIVE = 0 AND @CASE_SENSITIVE = 1 THEN CHARINDEX(@PATTERN, @DATA COLLATE French_CI_AS, @O)
ELSE CHARINDEX(@PATTERN, @DATA COLLATE French_CI_AI, @O)
END;
END;
-- mise à jour des données pour présentation
UPDATE @T
SET OCU_TEXTE = SPACE(@WIDE - LEN(OCU_AVANT)) + REVERSE(OCU_AVANT) + OCU_EXTRAIT + OCU_APRES,
OCU_AVANT = REPLICATE(CHAR(160), @WIDE - LEN(OCU_AVANT)) + REVERSE(OCU_AVANT);
RETURN;
END
GO
Voici deux autres façons de coder cette recherche sans tenir compte des problèmes de collation (ces fonctions utiliseront la collation de la colonne dans laquelle s’effectue la recherche).
CREATE FUNCTION dbo.F_COMPTE_LETTRE_1 (@STRING VARCHAR(8000),
@LETTRE CHAR(1))
RETURNS SMALLINT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @LEN SMALLINT,
@I SMALLINT = 0,
@J SMALLINT,
@NBR INT = 0;
SET @LEN = LEN(@STRING);
WHILE @I @I
BEGIN
SELECT @I = @J+1, @NBR += 1;
CONTINUE;
END
ELSE
BREAK;
END;
RETURN @NBR;
END
GO
et :
CREATE FUNCTION dbo.F_COMPTE_LETTRE_2 (@STRING VARCHAR(8000),
@LETTRE CHAR(1))
RETURNS SMALLINT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN LEN(@STRING) - LEN(REPLACE(@STRING, @LETTRE, ''));
END
GO
Sur une table comportant 1 250 000
fonction temps (ms)
----------------------- ----------
dbo.F_COMPTE_LETTRE_1 8 043
dbo.F_COMPTE_LETTRE_2 7 717
Le site web sur le SQL et les SGBDR
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’ntreprise SQL Spot
         Â