Recherches de multiples occurrences d’une chaine dans une autre

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

MVP Microsoft SQL Server


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

          

Laisser un commentaire