Stack Overflow
Stack Overflow

Questions tagged [stuff]

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntax

STUFF ( character_expression , start , length , replaceWith_expression )

Arguments

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start

Is an integer value that specifies the location to start deletion and insertion. If start is negative or zero, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

length

Is an integer that specifies the number of characters to delete. If length is negative, a null string is returned. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. If length is zero, insertion occurs at start location and no characters are deleted. length can be of type bigint.

replaceWith_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression replaces length characters of character_expression beginning at start. Providing NULL as the replaceWith_expression, removes characters without inserting anything.

Return Types

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

Remarks

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

Official Documentation

MSDN

48 questions
490
votes
8 answers

How Stuff and 'For Xml Path' work in SQL Server?

Table is: Id Name 1 aaa 1 bbb 1 ccc 1 ddd 1 eee Required output: Id abc 1 aaa,bbb,ccc,ddd,eee Query: SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, '' …
Puneet Chawla
  • 5,729
  • 4
  • 16
  • 33
2
votes
0 answers

After adding Feature Name and Feature Value query takes more than 5 minutes

I work on SQL Server 2014 and my issue occurred after displaying Feature Name and Feature Value separated by $. When executing the query below after adding Feature Name and Feature Value with stuff it became very slow. How to enhance it? Before…
abeer shlby
  • 41
  • 6
1
vote
2 answers

Order by on specific id in subquery with STUFF()

I am using SQL Server and facing problem to get my required data. Here is my sample table: Date PlayID Name City 11/20/2022 101 Rishi Mumbai 11/20/2022 102 Smita New…
Rahul Chaudhari
  • 148
  • 1
  • 17
1
vote
2 answers

SQL STUFF FOR XML with specific grouping

I'm working in SQL Server 2014, and I have the following simple data, which tracks calling history of users: PhoneNumber Activity ActivityDate ------------------------------------ 9075551234 Incoming 2022-04-01 9075551234 Outgoing …
Depth of Field
  • 307
  • 2
  • 16
1
vote
3 answers

SQL tagging - Returning new column in results

I need to create a new column for my query labeled Tags There are three tags and the definitions are below: Metro: City = Chicago Mailing: ACNT = 'ACT' Greeting: Salutation in ('Ms.','Mrs.') Current table: ID Salutation City State …
Toby
  • 135
  • 2
  • 17
1
vote
1 answer

Select distinct with order by in stuff function

I would like to know if its possible to reorder the concatenated cells in stuff function based on another column. As of now, I am using below query SELECT Id, KPI = STUFF((SELECT DISTINCT ', ' + Name FROM #TempTable1 b …
Justin Matthew Tacogue
  • 11
  • 1
1
vote
2 answers

Concatenate SQL columns with comma separated

Is there any alternate way to concatenate SQL columns with comma separated. I am using below logic for concatenation. The columns (col1,col2,col3) can have null values. select stuff( left(concat(col1,',',col2,',',col3), …
MRR
  • 83
  • 3
  • 9
1
vote
2 answers

T-SQL - only stuff / concatenate some columns and drop xml

I have a table like this: ID C1 C2 seq 1 This is my re ally long sentence 1 1 This is my re stored in a 2 1 This is my re really terrible format 3 And I need some SQL query or stored procedure to pull it out such that it reads "this…
Bob Fishel
  • 123
  • 2
  • 5
  • 15
1
vote
0 answers

After adding a function to use a renamed file, the program won't find the text string anymore

Okay, the title may be a bit vague. I honestly don't even know how to phrase the question. The program tries to find a string of text inside a file, and then print the entire line that the string is on. While testing, I created a file with the…
Mew
  • 25
  • 4
1
vote
2 answers

how to use "Stuff and 'For Xml Path'" to unite rows in table

Please help me to get united rows and list of accounts separated by commas in table. I don't quite understand how to use "Stuff and 'For Xml Path'" for it. This is my query: CREATE TABLE invoices ( invoice VARCHAR(20) NOT NULL, quantity INT NOT…
user421875
  • 15
  • 1
  • 5
1
vote
1 answer

SQL server query to search and stuff multiple rows

I have table employee_table which is like this org employeeid (int) firstname lastname 1234 56788934 Suresh Raina 1234 56793904 Virat Kohli then i have project_table which is like this Project …
curiousboy
  • 135
  • 2
  • 13
1
vote
1 answer

String aggregate in legacy in sql server

I have this table grid_id | criteria_id | start_value| end_value | provider | property1 | property2 | property3 --------|-------------|------------|-----------|----------|-----------|-----------|----------- 1 | 1 | 3 | …
White_Noise
  • 13
  • 3
1
vote
2 answers

Create comma separated value strings using data from different tables in SQL Server

I have the following database model: criteria table: criteria_id criteria_name is_range 1 product_category 0 2 product_subcategory 0 3 items 1 4 criteria_4 …
Frey_ja
  • 51
  • 13
1
vote
2 answers

How to separate unique values respective to its row using STUFF and FOR XML PATH

I'm running this query: SELECT A.PeripheralNumber, P.LoginName, P.FirstName, P.LastName, A.EnterpriseName, A.AgentDeskSettingsID, AttributeValues = STUFF((SELECT ',' + ATT.EnterpriseName+'='+AT.AttributeValue AS Attribute FROM Agent_Attribute…
aquamelli
  • 33
  • 1
  • 6
0
votes
1 answer

SQL Query slow only when Row_Number values are used in STUFF

I have a fairly basic SQL query which runs in 1 second without the Data_1 field which is performing a STUFF() and using RN for filter and order. With the Data_1 field in the query the execution goes from 1 second to 25 seconds. If I remove the RN…
JetRocket11
  • 302
  • 4
  • 17
1
2 3 4