Alias Names in SQL ServerBasic DDL Operations in MS SQL ServerDynamic SQLDynamic SQL PivotInstalling SQL Server on WindowsJSON in SQL ServerManaging Azure SQL DatabaseMicrosoft SQL Server Advanced optionsMicrosoft SQL Server Aggregate FunctionsMicrosoft SQL Server Analyzing a QueryMicrosoft SQL Server Backup and Restore DatabaseMicrosoft SQL Server bcp (bulk copy program) UtilityMicrosoft SQL Server BULK ImportMicrosoft SQL Server CASE StatementMicrosoft SQL Server CLUSTERED COLUMNSTOREMicrosoft SQL Server COALESCEMicrosoft SQL Server Common Language Runtime IntegrationMicrosoft SQL Server Common Table ExpressionsMicrosoft SQL Server Computed ColumnsMicrosoft SQL Server Converting data typesMicrosoft SQL Server CREATE VIEWMicrosoft SQL Server cross applyMicrosoft SQL Server CursorsMicrosoft SQL Server Data TypesMicrosoft SQL Server Database permissionsMicrosoft SQL Server Database SnapshotsMicrosoft SQL Server DatesMicrosoft SQL Server DBCCMicrosoft SQL Server DBMAILMicrosoft SQL Server Delimiting special characters and reserved wordsMicrosoft SQL Server Drop KeywordMicrosoft SQL Server Dynamic data maskingMicrosoft SQL Server EncryptionMicrosoft SQL Server Export data in txt file by using SQLCMDMicrosoft SQL Server File GroupMicrosoft SQL Server FilestreamMicrosoft SQL Server for JSONMicrosoft SQL Server for XML PATHMicrosoft SQL Server Foreign KeysMicrosoft SQL Server Full-Text IndexingMicrosoft SQL Server Generating a range of datesMicrosoft SQL Server GROUP byMicrosoft SQL Server IF...ELSEMicrosoft SQL Server In-Memory OLTP (Hekaton)Microsoft SQL Server IndexMicrosoft SQL Server InsertMicrosoft SQL Server INSERT INTOMicrosoft SQL Server IntroductionMicrosoft SQL Server Isolation levels and lockingMicrosoft SQL Server JoinMicrosoft SQL Server Last Inserted IdentityMicrosoft SQL Server Limit Result SetMicrosoft SQL Server Logical FunctionsMicrosoft SQL Server Management Studio Shortcut KeysMicrosoft SQL Server MERGEMicrosoft SQL Server MigrationMicrosoft SQL Server Modify JSON textMicrosoft SQL Server Move and copy data around tablesMicrosoft SQL Server Natively compiled modules (Hekaton)Microsoft SQL Server NULLsMicrosoft SQL Server OPENJSONMicrosoft SQL Server ORDER byMicrosoft SQL Server over ClauseMicrosoft SQL Server PaginationMicrosoft SQL Server ParsenameMicrosoft SQL Server PartitioningMicrosoft SQL Server Permissions and SecurityMicrosoft SQL Server PHANTOM readMicrosoft SQL Server PIVOT / UNPIVOTMicrosoft SQL Server Primary KeysMicrosoft SQL Server Privileges or PermissionsMicrosoft SQL Server Queries with JSON dataMicrosoft SQL Server Query HintsMicrosoft SQL Server Query StoreMicrosoft SQL Server Querying results by pageMicrosoft SQL Server Ranking FunctionsMicrosoft SQL Server Resource GovernorMicrosoft SQL Server Retrieve information about the databaseMicrosoft SQL Server Retrieve Information about your InstanceMicrosoft SQL Server Row-level securityMicrosoft SQL Server Scheduled Task or JobMicrosoft SQL Server SchemasMicrosoft SQL Server SCOPE IDENTITY()Microsoft SQL Server SELECT statementMicrosoft SQL Server SequencesMicrosoft SQL Server Service brokerMicrosoft SQL Server Sorting/ordering rowsMicrosoft SQL Server Spatial DataMicrosoft SQL Server SQLCMDMicrosoft SQL Server Stored Procedures

Microsoft SQL Server the STUFF Function

From WikiOD

Parameters[edit | edit source]

Parameter Details
character_expression the existing string in your data
start_position the position in character_expression to delete length and then insert the replacement_string
length the number of characters to delete from character_expression
replacement_string the sequence of characters to insert in character_expression

Using FOR XML to Concatenate Values from Multiple Rows[edit | edit source]

One common use for the FOR XML function is to concatenate the values of multiple rows.

Here's an example using the Customers table:

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

In the example above, FOR XML PATH()) is being used to concatenate email addresses, using ; as the delimiter character. Also, the purpose of STUFF is to remove the leading ; from the concatenated string. STUFF is also implicitly casting the concatenated string from XML to varchar.

Note: the result from the above example will be XML-encoded, meaning it will replace < characters with &lt; etc. If you don't want this, change FOR XML PATH()) to FOR XML PATH, TYPE).value('.[1]','varchar(MAX)'), e.g.:

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

This can be used to achieve a result similar to GROUP_CONCAT in MySQL or string_agg in PostgreSQL 9.0+, although we use subqueries instead of GROUP BY aggregates. (As an alternative, you can install a user-defined aggregate such as this one if you're looking for functionality closer to that of GROUP_CONCAT).

Basic Character Replacement with STUFF()[edit | edit source]

The STUFF() function inserts a string into another string by first deleting a specified number of characters. The following example, deletes "Svr" and replaces it with "Server". This happens by specifying the start_position and length of the replacement.

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

Executing this example will result in returning SQL Server Documentation instead of SQL Svr Documentation.

Basic Example of STUFF() function.[edit | edit source]

STUFF(Original_Expression, Start, Length, Replacement_expression)

STUFF() function inserts Replacement_expression, at the start position specified, along with removing the characters specified using Length parameter.

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

Executing this example will result in returning the given table

FirstName LastName Email StuffedEmail
Jomes Hunter J*****
Shyam rathod S*****
Ram shinde R*****

Obtain column names separated with comma (not a list)[edit | edit source]

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

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

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

stuff for comma separated in sql server[edit | edit source]

FOR XML PATH and STUFF to concatenate the multiple rows into a single row:

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