Sql-server – Stuff function in T-SQL

sql servert-sql

select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)),  6)
            , 3, 0, ':')
        , 6, 0, ':') 
    AS [LastRunDuration (HH:MM:SS)]
 from sysjobhistory

The query I have given above works fine but I am trying to understand it more clear. My observations are:

First we cast the run duration into varchar and with a length 6 and append six 0's to right of it which becomes if its is 10 then 00000010
After we use the stuff function to insert : in between this here it is at the 3rd position so the above one becomes 00:000010
Again we use stuff to insert the : symbol to the sixth position so it becomes 00:00:0010

But when I run the query if there is a values 10 it shows as 00:00:10.

How that can be?

Best Answer

In your first bullet you say the value becomes 00000010. That is incorrect. It actually becomes 000010 because of the RIGHT( , 6).

To clarify, compare the difference:

SELECT LEFT('00000010', 6), RIGHT('00000010', 6);

Results:

------  ------
000000  000010

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

Sql-server – Can’t seem to understand why STUFF isn’t working

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              INNER JOIN SurveyQuestion sq
                ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
              WHERE x.SurveyQuestionID = sq.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Or to shorten the code, you could even rewrite the query to:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              WHERE x.SurveyQuestionID = sqpa.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestion AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Both will give you the same result.

Best Answer

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

Sql-server – Can’t seem to understand why STUFF isn’t working

Related Question