October 26, 2005 at 4:27 pm
Hi,
I'm unable to get the complete results in my attempt to concantenate two NVarChar(4000) fields. Results from the first field are displayed properly, but nothing from the second field (named Html2) is returned (I've confirmed that data does exist in the second field).
Here's the statement I'm using:
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT [Html] + Html2 AS [HTML]
FROM JOBS_Openings
JOIN JOBS_Html ON JOBS_Openings.ID = JOBS_Html.OpeningsID
LEFT JOIN JOBS_Html2 ON JOBS_Html.OpeningsID = JOBS_Html2.OpeningsID
WHERE ID = 2008
SET CONCAT_NULL_YIELDS_NULL ON
Please note that the first field (named [Html]) is used up to its 4000 character capacity.
I'd appreciate any insights on what might be causing this problem and suggestions on how I can get around it (although, if possible, I want to avoid converting the data type in the applicable tables to Text).
Thanks in advance for any help!
October 26, 2005 at 4:51 pm
This not possible.
You cannot concatenate two NVarChar(4000) into a single NVarchar(4000)
You also cannot put 2 liters into a 1 liter container (or 2 gallons into a 1 gallon container)
SQL = Scarcely Qualifies as a Language
October 26, 2005 at 5:22 pm
Thanks for the reply, but I'm not attempting to concatenate two NVarChar(4000) fields into a single NVarChar(4000) field. I'm attempting to concatenate the data in these two fields for display on a web page.
Still looking for insight/suggestions. Thanks again.
October 26, 2005 at 5:59 pm
Carl answered it actually...even after the concatenation the total length cannot exceed nvarchar(4000), that is why you are seeing data only from the first field. The truncation would occur at nvarchar(4000) limit.
And the same will be the case if you were trying to concatenate two columns each of varchar(8000).
You can fetch them as two separate columns and do the concatenation at the client application side. But why do you need to display/use the 8000 (potentially) character string ?
October 27, 2005 at 2:09 pm
Thanks for the help. I wasn't aware that a limitation existed on the total number of characters that could be concatenated in a Select statement...good to know.
To answer your question, the data I'm trying to display was imported from another database's Text field into two NVarChar(4000) fields since Text fields aren't supported by many of the Sql Server string functions and can be difficult to work with in other circumstances.
Thanks again for your time. I really appreciate it.
October 27, 2005 at 3:41 pm
It is NOT a limitation of the SELECT statement.
It is a limitation of the nvarchar datatype.
October 27, 2005 at 4:22 pm
Forgive me Phil, but I'm not sure what you mean.
I'm clear on the fact that I can't put more than 4000 characters into a NVarChar field...no confusion there.
However, you and Carl seem to be assuming some knowledge on my part that isn't there???
Should I infer from your comments that when you concatenate two character fields in a Select statement that the result is placed in a temporary character field of the same data type as the original character fields and the same maximum character limitation as the original character fields?
For example, if Field1 and Field2 are both NVarChar(1000) fields, a Select that concatenates these two fields returns data placed in a temporary NVarChar(4000) Field?
If the answer is yes, then that will clear it up your comments for me. If the answer is no, then I'm afraid that I'm still confused.
I'd appreciate any further clarification you'd like to provide.
October 27, 2005 at 8:40 pm
All we are saying is that when concatenation of string fields is done, the final result after the concatenation is still bound to the length limitations of the data-type...so, if two nvarchar(4000) fields are concatenated, the final string cannot be more than nvarchar(4000), if two varchar(8000) fields are concatenated the final string cannot be more than varchar(8000)....truncation of data will occur when that limit is raised.
It is the limitation of the data-type not the select statement.
October 31, 2005 at 4:26 pm
I'm on board. Thanks again for the help.
October 31, 2005 at 5:52 pm
exactly
If you were attempting to concatenate two variables or columns of varchar(10), you would need to make the first column varchar(20) before concatenating:
declare @v1 varchar(10)
declare @v2 varchar(10)
set @v1 = replicate('1', 10)
set @v2 = replicate('2', 10)
select @v1 + @v2 -- Truncate at 10 characters
, cast(@v1 as varchar(20) ) + @v2
SQL = Scarcely Qualifies as a Language
November 1, 2005 at 10:51 am
Thanks Carl, but I ran your example in query analyzer and it returned 20 characters in the first Select (the one without the cast)???
declare @v1 varchar(10)
declare @v2 varchar(10)
set @v1 = replicate('1', 10)
set @v2 = replicate('2', 10)
select @v1 + @v2 --returns 20 characters
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply