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
0
votes
1 answer

Stuff() Not Grouping Accurately

I am using an older version of SQL Server and trying to convert rows to concatenated columns. From researching here on stack overflow I see that I should be using STUFF(). However, when I attempt to replicate the answers I found here, I can't get…
JayA
  • 19
  • 6
0
votes
0 answers

Multiple case expression and stuff functions in select statement cause very bad performance

I'm trying to combine multiple rows and multiple columns into one column. I've a table like below: Parent table: ID Name 1 A 2 B Child table: ID Score1 Score2 Remark1 Remark2 1 0 0 Good1! Excellent1! 1 0 0 Good1! …
xChaax
  • 193
  • 5
  • 27
0
votes
0 answers

Stuff function returns null values

When i am trying the stuff function in this query: select Project.* from TimePayrollTimesheet3 tp inner join (SELECT t1.parentrecordID, abc = STUFF ( (SELECT ',' + caprojectnumber FROM…
shweta
  • 11
0
votes
0 answers

Why selecting STUFF value decrease performance of a query in SQL Server

Recently I want to improve some of my queries and I see something that I don't really understand, here is my query : SELECT S.VENTE_GUID, Top6QtySold.list FROM SALES s with(nolock) OUTER APPLY (SELECT STUFF((SELECT TOP(6)…
ElRoro
  • 203
  • 1
  • 13
0
votes
1 answer

Replace character in EUR amount

First post so hope I include the right stuff, apologies if not. I have a mix of currency amounts Im importing into a database. Some of them are EUR and as such swap the "," and "." positions for decimal and 1,000 separator. I need to replace the ","…
SFish1980
  • 1
0
votes
1 answer

What is the best way to replicate the STUFF() function from T-SQL in PL/SQL?

I'm trying to replicate the behavior of the STUFF() function from Transact SQL in Oracle. I assume both the CONCAT and SUBSTR functions will be involved, but I can't figure out a clever way to do it without writing too much code. Does anyone know a…
Seyi
  • 9
  • 3
0
votes
1 answer

How to order by nulls-last by a linked table

I have two tables Person and PersonSkill Person ID NAME 1 Person 1 2 Person 2 3 Person 3 PersonSkill PERSON_ID SKILL SORT 1 Sing 20 1 Playful 10 2 Sing 10 1 Bowl 30 1 SQL 40 I'm trying to write a order by which…
Jeggy
  • 1,474
  • 1
  • 19
  • 35
0
votes
1 answer

Replace XML Path Stuff Query

I would like to replace the following query as I have performance problems. The execution plan is giving me a lot of redisual due to Table Spool operators (Lazy Spool) SELECT DISTINCT LOT_0, VarCode = STUFF( ( SELECT DISTINCT ', ' +…
Jose Navarro
  • 27
  • 2
0
votes
2 answers

Need to add double quotes to SQL SERVER string values

I have integer values that are being passed from a parameter that needed to be input as a string and padded so that they are 7 digits. This will then be passed into another query. declare @t table(ctl_num int) insert into @t values(5675,…
jackstraw22
  • 517
  • 10
  • 30
0
votes
1 answer

Combining Multiple Rows into 1 Row (but twice)

My query looks like this: SELECT req.number , task.number , usr.vendor FROM req LEFT JOIN task on req.sys_id = task.req_item LEFT JOIN usr on usr.sys_id = task.assigned_to Results look like this: I want resulst to look like…
Chrissy Scott
  • 29
  • 5
0
votes
1 answer

Is there a way in the STUFF(... FOR XML) function in SQL to prevent duplicate values?

To keep this short, here's my SQL code: SELECT EmailAddress, FormsSubmitted = STUFF( ( SELECT ',' + SourceSubType FROM UK_AGT_AgentForms_TEST_DE a WHERE a.EmailAddress = b.EmailAddress FOR XML…
Mike Marks
  • 10,017
  • 17
  • 69
  • 128
0
votes
0 answers

How can we extract the value from a stuff function variable for processing

Hello I would like to know how can we assign the value of a column to a variable in SQL server 2012 For example consider the below scenario in which I retrieve the record id and emails (multiple emails as comma separated in single row) for a list of…
John George
  • 1
  • 2
0
votes
2 answers

Convert the data, split rows into columns in SQL

Could you please help me in displaying the table output in below format. Table data Required output
MRR
  • 83
  • 3
  • 9
0
votes
1 answer

SQL Query to group by time and roll up and concatenate string values

I am trying to get a particular format from a group of times and days between two tables. Database: MeetingTime table has a relationship from MeetingTime.DayOfWeekId (foreign key) to table DayOfWeek.Id (Primary Key). Example Query: select…
cmartin
  • 2,819
  • 1
  • 26
  • 31
0
votes
1 answer

How can i avoid a multiple using stuff for xml path?

I'm having a problem with sql query. What i'm trying to do is to get list of stores with tasks assigned on users in it. Each role in separated column with user names. I'm using STUFF from getting concatenated row but it looks like bad idea for 100K+…
Denis Mishin
  • 71
  • 5
1
2
3 4