Microsoft SQL Server
La fonction STUFF

Paramètres

Remplacement de caractères de base avec STUFF ()

La fonction STUFF() insère une chaîne dans une autre chaîne en supprimant d’abord un nombre spécifié de caractères. L'exemple suivant supprime "Svr" et le remplace par "Serveur". Cela se produit en spécifiant la position de start_position et la length du remplacement.

SELECT STUFF('SQL Svr Documentation', 5, 3, 'Server')

L'exécution de cet exemple entraînera le renvoi de la SQL Server Documentation au lieu de la SQL Svr Documentation.

Utilisation de FOR XML pour concaténer des valeurs à partir de plusieurs lignes

Une utilisation courante de la fonction FOR XML consiste à concaténer les valeurs de plusieurs lignes.

Voici un exemple d'utilisation de la table Customers :

SELECT 
    STUFF( (SELECT ';' + Email
        FROM Customers 
        where (Email is not null and Email <> '')
        ORDER BY Email ASC 
        FOR XML PATH('')), 
    1, 1, '')

Dans l'exemple ci-dessus, FOR XML PATH('')) est utilisé pour concaténer les adresses électroniques, en utilisant ; comme caractère de délimiteur. En outre, le but de STUFF est de supprimer le principal ; de la chaîne concaténée. STUFF également implicitement la chaîne concaténée de XML à varchar.

Remarque: le résultat de l'exemple ci-dessus sera codé en XML, ce qui signifie qu'il remplacera < caractères par < etc. Si vous ne le voulez pas, modifiez FOR XML PATH('')) en FOR XML PATH, TYPE).value('.[1]','varchar(MAX)') , par exemple:

SELECT 
    STUFF( (SELECT ';' + Email
        FROM Customers 
        where (Email is not null and Email <> '')
        ORDER BY Email ASC 
        FOR XML PATH, TYPE).value('.[1]','varchar(900)'),
    1, 1, '')

Cela peut être utilisé pour obtenir un résultat similaire à GROUP_CONCAT dans MySQL ou string_agg dans PostgreSQL 9.0+, bien que nous utilisions des sous-requêtes au lieu des agrégats GROUP BY. (En guise d'alternative, vous pouvez installer un agrégat défini par l'utilisateur tel que celui-ci si vous recherchez une fonctionnalité plus proche de celle de GROUP_CONCAT ).

Obtenir des noms de colonne séparés par une virgule (pas une liste)

/*
The result can be use for fast way to use columns on Insertion/Updates.
Works with tables and views.

Example: eTableColumns  'Customers'
ColumnNames
------------------------------------------------------
Id, FName, LName, Email, PhoneNumber, PreferredContact

INSERT INTO Customers (Id, FName, LName, Email, PhoneNumber, PreferredContact)
    VALUES (5, 'Ringo', 'Star', '[email protected]', NULL, 'EMAIL')
*/
CREATE PROCEDURE eTableColumns (@Table VARCHAR(100))
AS
SELECT ColumnNames = 
   STUFF( (SELECT ', ' +  c.name
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID( @Table)
        FOR XML PATH, TYPE).value('.[1]','varchar(2000)'),
    1, 1, '')
GO

choses pour les virgules séparées dans le serveur SQL

FOR XML PATH et STUFF pour concaténer les plusieurs lignes en une seule ligne:

  select distinct t1.id,
      STUFF(
             (SELECT ', ' + convert(varchar(10), t2.date, 120)
              FROM yourtable t2
              where t1.id = t2.id
              FOR XML PATH (''))
              , 1, 1, '')  AS date
    from yourtable t1;

Exemple de base de la fonction STUFF ().

STUFF (Original_Expression, Start, Length, Replacement_expression)

La fonction STUFF () insère l’expression Remplacement_, à la position de début spécifiée, et supprime les caractères spécifiés à l’aide du paramètre Longueur.

 Select FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail From Employee

L'exécution de cet exemple entraînera le renvoi de la table donnée