SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenating a varchar(max) column


Concatenating a varchar(max) column

Author
Message
jonathon
jonathon
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
Hi, I'm having trouble concatenating multiple rows in a table into a single varchar(max) variable. I am trying to concat several other columns (date etc) with some character formatting and output the entire result. My problem is that the output always seems to get truncated; sometimes around 500 characters. Here is the code I am using;

   DECLARE @Summary varchar(MAX),
@count int, /*create local @@fetch_status*/
@iRow INT, /*row pointer (index)*/
@KeyValue Varchar(20),
@UserTitle VARCHAR(20),
@Date VARCHAR(20),
@details VARCHAR(MAX)

SET @Summary = ''

DECLARE @Reccom TABLE(
RowID INT IDENTITY(1, 1),
KeyValue VARCHAR(20),
UserTitle VARCHAR(50),
Date VARCHAR(20),
Details VARCHAR(MAX))

INSERT INTO @Reccom

SELECT cd.KeyValue,
rec.userTitle AS UserTitle,
CONVERT(varchar(20), rec.RecommendationDate, 103) AS Date,
rec.Details

from t_table rec
left outer join t_ControlData cd on rec.code= cd.KeyCode and cd.KeyName = 'name'
where rec.id= 2508
order by cd.KeyIndex ASC


/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1

--loop though the active components of the drug
WHILE @iRow <= @count
BEGIN
SELECT @KeyValue = KeyValue,
@UserTitle = UserTitle,
@Date = Date,
@Details = Details
FROM @Reccom
WHERE RowID = @iRow

SELECT @Summary = @Summary + @KeyValue + ' Note - ' + @UserTitle + ', ' + @Date + CHAR(13)

+ @details + CHAR(13)

--increment the counter
SELECT @iRow = @iRow + 1
END



Any help would be appreciated
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44077 Visits: 14925
Are you just doing this in SSMS? If so the SSMS query window only output a certain number of characters. If you go into tools -> options and then go to query results -> Sql Server there are settings under Results to Grids and Results to Text for the size of data to display.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Bevan Keighley
Bevan Keighley
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 834
Hi,

I'm not sure what else you are doing with this data - but I don't think you need to loop through all your rows and put everything into a table variable as you have.

Try something like this (it should be roughly correct, but check the columns against your database). This concatenates all the values (make sure they are varchar) and then joins the next row on... etc


DECLARE @var varchar(max)

SELECT @var = COALESCE(@var + ', ', '') + cd.KeyValue + ' Note - ' + rec.userTitle + ', ' + CONVERT(varchar(20), rec.RecommendationDate, 103) + CHAR(13) + rec.Details + CHAR(13)
FROM t_table rec LEFT OUTER JOIN t_ControlData cd on rec.code= cd.KeyCode and cd.KeyName = 'name'
WHERE rec.id= 2508 ORDER BY cd.KeyIndex ASC

SELECT @var




B
jonathon
jonathon
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
Thanks guys, tried both those ideas however Jack, the limit is massive and checking len(@Summary) at the end of the code (not posted) shows my lengths to be tiny..

B, I tried that solution.. in the DB, for testing, 5 rows match the record, all with the "Details" fields set to "abcdefghijklmnopqrstuvwxyZabcdefghijklmnopqrstuvwxyZabcdefghijklmnopqrstuvwxyZ"

this is the output I get from your implementation...

, user Note - Paul user, 28/03/2007
abcdefghijklmnopqrstuvwxyZabcdefghijklmnopqrstuvwxyZabcdefghijklmnopqrstuvwxyZ

Only one row! This is the same as what I found when I tried to concatenate using the select query itself:

SELECT @Summary = @Summary + @KeyValue + ' Note - ' + @UserTitle + ', '  + @Date + CHAR(13) etc..


Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44077 Visits: 14925
Can you post some sample data? Like suggested in the first link in my signature.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
jonathon
jonathon
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
I tested with the code below and varchar(max) acts as expected when concatenating with the + operator when the number of rows are known.. However a programmatic solution still seems elusive...

DECLARE @C VARCHAR(MAX)
DECLARE @Code VARCHAR(20)
DECLARE @Value VARCHAR(MAX)


DECLARE @Temp TABLE(
RowID INT IDENTITY(1, 1),
Code VARCHAR(20),
MaxValue VARCHAR(MAX))

INSERT INTO @Temp (
Code,
MaxValue
) VALUES (
/* Code - VARCHAR(20) */ 'A note',
/* Value - VARCHAR(MAX) */ REPLICATE(CAST('A' AS VARCHAR(MAX)), 10000 ) )

INSERT INTO @Temp (
Code,
MaxValue
) VALUES (
/* Code - VARCHAR(20) */ 'B note',
/* Value - VARCHAR(MAX) */ REPLICATE(CAST('B' AS VARCHAR(MAX)), 10000 ) )

SET @C = ''

SELECT @Code = Code, @Value=MaxValue FROM @Temp WHERE RowID = 1
SELECT @C = @Code + @Value
SELECT @Code = Code, @Value=MaxValue FROM @Temp WHERE RowID = 2
SELECT @C = @C + @Code + @Value

SELECT @C
SELECT LEN(@C)



len output: 20012
jonathon
jonathon
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
Sure thing jack just use multiples of this:

   INSERT INTO @Reccom (
KeyValue,
UserTitle,
Date,
Details
) VALUES (
'Doctor Note',
'Mr Bob Saggett',
'23/10/1986',
CAST('the brown fox jumped over the silky terrier' AS VARCHAR(MAX)) )



The data itself isn't so important, but the details field can grow to be larger than 8000 chars, hence the use of max.
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44077 Visits: 14925
This worked for me:


Declare @Reccom table (KeyValue varchar(20), USerTitle varchar(25), Date varchar(14), Details varchar(max))
Declare @temp table(row_id int,KeyValue varchar(20), USerTitle varchar(25), Date varchar(14), Details varchar(max))
Declare @count int
Declare @data varchar(max)

Set @count = 1

WHile @Count <= 10
Begin
INSERT INTO @Reccom (
KeyValue,
UserTitle,
Date,
Details
) VALUES
(
'Doctor Note',
'Mr Bob Saggett',
'23/10/1986',
CAST('the brown fox jumped over the silky terrier' AS VARCHAR(MAX))
)

Set @count = @count + 1
End

Set @count = 1
Set @data = ''

Insert into @temp
Select
row_number() Over(Order By Date) as row_id,
*
From
@Reccom

While @count <= (Select Max(row_id) From @temp)
Begin
Select
@data = @data + keyvalue + ' Note - ' + usertitle + ', ' +
date + char(13) + details + char(13)
From
@temp

Set @count = @count + 1
End

Select Len(@data), @data





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
jonathon
jonathon
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
Thanks heaps Jack, that seems to work perfectly Smile Many thanks
ravi090191
ravi090191
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 2
what is the limitation of concatenation which is causing below issue?
Any help?



This query does not work:
SET @tSprocSQL1 = @tMidSQL1+@tMidSQL2+@tMidSQL3+@tMidSQL4+@tMidSQL5+@tMidSQL6+@tMidSQL7+@tMidSQL8+@tMidSQL9+@tMidSQL10+@tEndSproc+ CHAR(13);

Whereas query works:
SET @tSprocSQL1 = @tMidSQL1+@tMidSQL2+@tMidSQL3+@tMidSQL4+@tMidSQL5+@tMidSQL6+@tMidSQL7+@tMidSQL8
SET @tSprocSQL1 = @tSprocSQL1+@tMidSQL9+@tMidSQL10+@tEndSproc+ CHAR(13);

___________________________________________________________
@tSprocSQL1 - is nvarchar(max)
while all tMidSQL are navarchar(4000)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search