String concatenation in Transact-SQL

-- Initialize table.
CREATE TABLE #BigStrings
(
	StringID bigint IDENTITY(1,1) NOT NULL,
	StringValue text NOT NULL,
	CONSTRAINT PK_BigStrings PRIMARY KEY CLUSTERED 
	(
		StringID ASC
	)
) ON [PRIMARY]

-- Some sample data.
DECLARE @SampleStrings AS TABLE (StringID int IDENTITY(1, 1), StringField varchar(100))
INSERT INTO @SampleStrings
SELECT SampleValue FROM
(
	SELECT 'a' AS SampleValue
	UNION ALL SELECT 'b'
	UNION ALL SELECT 'c'
	UNION ALL SELECT '123'
) AS SampleTable

-- Variables.
DECLARE @StringID AS bigint
DECLARE @StringPointer AS binary(16)
DECLARE @StringValue AS varchar(8000)
DECLARE @StringOffset AS int
DECLARE @StringLength AS int

-- Initialize.
SET @StringOffset = 0
SELECT @StringLength = SUM(LEN(StringField)) FROM @SampleStrings

-- Pre-allocate the required string length.
INSERT INTO #BigStrings(StringValue) VALUES(REPLICATE(' ', @StringLength))

-- Get pointer to text.
SET @StringID = SCOPE_IDENTITY()
SELECT @StringPointer = TEXTPTR(StringValue)
FROM #BigStrings WHERE StringID = @StringID

-- Loop through each input string segment.
DECLARE SampleCursor CURSOR FOR
SELECT StringField FROM @SampleStrings
ORDER BY StringID ASC
OPEN SampleCursor
FETCH NEXT FROM SampleCursor INTO @StringValue
WHILE @@FETCH_STATUS = 0
BEGIN
	
	-- Update main string with string segment.
	UPDATETEXT #BigStrings.StringValue @StringPointer @StringOffset 0 @StringValue
	SET @StringOffset = @StringOffset + LEN(@StringValue)
	FETCH NEXT FROM SampleCursor INTO @StringValue
	
END
CLOSE SampleCursor
DEALLOCATE SampleCursor

-- Show concatenated string.
DECLARE @Result AS VARCHAR(8000)
SELECT @Result = StringValue FROM #BigStrings WHERE StringID = @StringID
DELETE FROM #BigStrings WHERE StringID = @StringID
SELECT @Result

-- Done with temporary table.
DROP TABLE #BigStrings

• Trailing spaces aren't handled well.
• I wouldn't recommend using it unless your SQL environment does not allow for XML processing.
• I only used a temporary table to make the example self-contained. You can instead use a normal table if you like.
• This example, which was tested in SQL Server 2000, is limited to 8000 characters just because I'm converting the result to a varchar and SQL Server 2000 has a maximum size of 8000 characters for that data type. However, even in SQL Server 2000, you need not convert to a varchar, so the length need not be limited if you are willing to deal with the text data type.
• Since the data in temporary tables (and normal tables) is written to the hard drive, performance may actually be worse than simple string concatenation for a small number of string segments.