Microsoft SQL Server
STUFF機能

パラメーター

STUFF（）による基本的な文字の置換

STUFF()関数は、指定された文字数を最初に削除することによって、文字列を別の文字列に挿入します。次の例では、 "Svr"を削除し、それを "Server"に置き換えます。これは、置換のstart_positionとlengthを指定することによって行われます。

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

この例を実行すると、 SQL Svr Documentation.代わりにSQL Svr Documentation. SQL Server Documentationが返されSQL Server Documentation SQL Svr Documentation.

FOR XMLを使用した複数行の値の連結

FOR XML関数の一般的な使用方法は、複数の行の値を連結することです。

次に、 Customersテーブルを使用した例を示します 。

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

上記の例では、 FOR XML PATH(''))が電子メールアドレスを連結するために使用されてい;区切り文字として使用します。また、 STUFFの目的は、先頭を取り除くこと;連結された文字列からSTUFFは、連結された文字列をXMLからvarcharに暗黙的にキャストしています。

注：上の例の結果はXMLエンコードされているため、 <文字を< FOR XML PATH, TYPE).value('.[1]','varchar(MAX)') （例： FOR XML PATH(''))

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, '')

これは、と同様の結果を達成するために使用することができGROUP_CONCAT MySQLの中やstring_agg我々は集計BY代わりにGROUPのサブクエリを使用していますが、PostgreSQLの9.0以降で。 （代わりに、 GROUP_CONCAT機能に近い機能を探している場合は、 このようなユーザー定義の集約をインストールできます）。

コンマで区切られた列名を取得する（リストではない）

/*
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

SQL Serverで区切られたコンマ用のもの

FOR XML PATHおよびSTUFFを使用して、複数の行を1つの行に連結します。

  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;

STUFF（）関数の基本例

STUFF（オリジナル_開始、長さ、置換_式）

STUFF（）関数は、指定された開始位置にReplacement_expressionを挿入し、Lengthパラメータを使用して指定された文字を削除します。

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

この例を実行すると、与えられたテーブルが返されます