Business Intelligence

[T-SQL] – Mise à jour avec la clause .WRITE ou la fonction STUFF

Fév 15, 2017

Nacera Benaoudia

Pour effectuer des mises à jour dans SQL Server la commande ‘’Update’’ est souvent la plus utilisée par les développeurs.

Mais il existe bien d’autres manières intéressantes pour mettre à jour partiellement ou complètement des chaînes de caractères statiques ou dynamiques.

Je fais référence à la fonction STUFF et la clause .WRITE dans SQL Server : chacune des deux permet d’insérer une chaîne de caractère dans une autre chaîne, après suppression d’un nombre de caractère spécifié dans l’un de ses paramètres.

Voici les syntaxes sans rentrer trop dans les détails (définitions disponibles sur MSDN) :

Syntaxe de la fonction STUFF:

STUFF (La chaîne de caractère initiale, position du début de transformation, longueur de la chaîne à supprimer, l’expression qui remplacera la chaîne à supprimer)

Syntaxe de la clause .WRITE:

Update table_Name

SET column_name.WRITE (expression, @Offset, @Length)

La chaîne spécifiée dans le paramètre expression remplace @Length caractères de la colonne : column_name à partir de la position précisée dans le paramètre @Offset

  1. En plus de cette différence de syntaxe, il faut signaler que :La fonctionSTUFFpermet d’effectuer une mise à jour partielle ou complète sur des chaînes de caractère de type char, varchar ou binaire. Alors que la clause .WRITE permet d’effectuer des modifications similaires mais sur des chaînes de caractères de type nchar(max),nvarchar(max), varbinary(max).
  2. La chaîne de caractère initiale de la fonctionSTUFFpeut être le résultat d’une requête SQL, ce que ne peut pas être le cas pour la colonne ou l’expression en entrée de la clause .WRITE. Par exemple:STUFF (
    (select ‘,’+ Id from [ps_Customer]
    where id_customer in (1,2,3) FOR XML PATH( »)), 1, 1,  »)
  3. Le résultat est différents si la valeur de la position de début est supérieure à la longueur de la chaîne à modifier : une erreur pour la clause .WRITE et la chaîne « NULL » pour la fonction STUFF.

Je voulais particulièrement revenir sur le fonctionnement de la clause «.WRITE» dans cet article; pour cela j’ai essayé de traiter les cas qui peuvent se présenter en attribuant les différentes valeurs possibles aux deux paramètres @Offset et @Length.

Pour cela on va tout d’abord créer une table Employee_Test avec une colonne Title qui sera de type nvarchar(max) :

2

On insère 8 lignes qui correspondront au 8 cas qui seront présentés :

3

Cas 1 (@Offset = NULL) :

La chaîne de caractère sera ajoutée à la fin de la chaîne existante quelle que soit la valeur de @Length.

4

Cas 2 (@Offset = 0, @Length = 0) :

La chaîne de caractère sera ajoutée au début de la valeur existante.

5

Cas 3 (@Offset = 0, @Length > 0) :

La chaîne de caractère (‘; ‘) remplacera @Length (5) caractères à partir de @Offset (0).

6

Cas 4 (@Offset<0) :

Retourne une erreur.

7

Cas 5 (@Length=0) :

La chaîne de caractère est insérée à la position @Offset, en gardant l’intégralité de la chaîne existante.

8

Cas 6 (@Offset>0, @ Length>0) :

La chaîne de caractère ‘ABCDEFGHIJ’ remplace @Length (3) caractères de la chaîne existante ‘Title6’ à partir de la position @Offset (1).

9

Cas 7 (@Length= NULL) :

La chaîne de caractère existante sera remplacée à partir de position @Offset jusqu’à la fin.

10

Cas 8 (@Length<0) :

Retourne une erreur.

11

 

 

Liens utiles sur le sujet :

https://technet.microsoft.com/en-us/library/ms177523.aspx

https://msdn.microsoft.com/fr-fr/library/ms188043(v=sql.120).aspx

0 commentaires

Soumettre un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Découvrez nos autres articles

Aller au contenu principal