Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

String concatenation in Transact-SQL

, 9 Mar 2011
Rate this:
Please Sign up or sign in to vote.
I like the original solution, especially when compared to this (which I am posting just to show that there are other solutions):-- Initialize table.CREATE TABLE #BigStrings( StringID bigint IDENTITY(1,1) NOT NULL, StringValue text NOT NULL, CONSTRAINT PK_BigStrings PRIMARY KEY...
I like the original solution, especially when compared to this (which I am posting just to show that there are other solutions):
-- 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
 
Here are some notes about the above code:
  • 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

AspDotNetDev
Web Developer
United States United States
  • Managing Your JavaScript Library in ASP.NET (if you work with ASP.net and you don't read that, you are dead to me).
  • Graduated summa cum laude with a BS in Computer Science.
  • Wrote some articles and some tips.
  • DDR ("New high score? What does that mean? Did I break it?"), ping pong, and volleyball enthusiast.
  • Software I have donated to (you should too):

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 10 Mar 2011
Article Copyright 2011 by AspDotNetDev
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid