February 23, 2010 at 1:54 am
Can anybody point me in the right direction, I am using replace on a nvarchar(max) field but the results are getting truncated. I have tried casting all the variables to nvarchar(max) to no avail.
drop table #mytable
CREATE TABLE #mytable
(Fulldescription nvarchar(max))
INSERT INTO #mytable
SELECT 'Mary had a little lamb, its fleece was white
as snow and every where
that Mary went
the little lamb would go
WxDxH: 100x100x100mm
Product Weight:
Dimensions: 1
UNSPC Code: 74551602'
select replace(fulldescription,char(13),'RETURN')
from #mytable
select replace(fulldescription,char(13),'LargerReplaceString')
from #mytable
-- this gets truncated
select replace(fulldescription,cast(char(13) as nvarchar(max)),cast('LargerReplaceString' as nvarchar(max)))
from #mytable
-- this truncates too
February 23, 2010 at 4:24 am
Hi,
This could be caused by the TEXTSIZE setting. Try: SELECT @@TEXTSIZE to see what you've got and adjust with SET TEXTSIZE x where x is the necessary length in bytes.
February 23, 2010 at 4:36 am
ohack
@@textsize comes back at 2M+
Setting text size has no effect.
February 23, 2010 at 4:43 am
Alan Stanley (2/23/2010)
Can anybody point me in the right direction, I am using replace on a nvarchar(max) field but the results are getting truncated. I have tried casting all the variables to nvarchar(max) to no avail.
drop table #mytable
CREATE TABLE #mytable
(Fulldescription nvarchar(max))
INSERT INTO #mytable
SELECT 'Mary had a little lamb, its fleece was white
as snow and every where
that Mary went
the little lamb would go
WxDxH: 100x100x100mm
Product Weight:
Dimensions: 1
UNSPC Code: 74551602'
select replace(fulldescription,char(13),'RETURN')
from #mytable
select replace(fulldescription,char(13),'LargerReplaceString')
from #mytable
-- this gets truncated
select replace(fulldescription,cast(char(13) as nvarchar(max)),cast('LargerReplaceString' as nvarchar(max)))
from #mytable
-- this truncates too
I have tried running the same code..and the results do not get truncated in either of the queries
--Divya
February 23, 2010 at 7:46 am
Can anybody suggest where to look - it happens on an out of the box SQL 2008 as well
Codepage is Latin1_General_CI_AS
everything else seems pretty standard
February 23, 2010 at 9:12 am
What exactly do you mean by "getting truncated"? Since you only do a SELECT it might be the influence of the length setting for the output of SSMS (Management Studio)....
Here are the results on my machine when setting the result window to "Results to text" mode (seems to be the expected result...).
When checking the result set length I get 22 for the first statement and 314 for the other two. So it seems to be an issue of text length setting... Try to change the setting for data length: Tools->Options->QueryResults->SQLServer->ResultsToText/ResultsToText to avalue larger than 320 and run it again
(1 row(s) affected)
---------------------------------------------------------------------------
Mary had a little lamb, its fleece was whiteRETURN
as snow and every whereRETURN
that Mary wentRETURN
the little lamb would goRETURN
WxDxH: 100x100x100mmRETURN
Product Weight: RETURN
Dimensions: 1RETURN
UNSPC Code: 74551602
(1 row(s) affected)
---------------------------------------------------------------------------
Mary had a little lamb, its fleece was whiteLargerReplaceString
as snow and every whereLargerReplaceString
that Mary wentLargerReplaceString
the little lamb would goLargerReplaceString
WxDxH: 100x100x100mmLargerReplaceString
Product Weight: LargerReplaceSt
(1 row(s) affected)
---------------------------------------------------------------------------
Mary had a little lamb, its fleece was whiteLargerReplaceString
as snow and every whereLargerReplaceString
that Mary wentLargerReplaceString
the little lamb would goLargerReplaceString
WxDxH: 100x100x100mmLargerReplaceString
Product Weight: LargerReplaceSt
(1 row(s) affected)
February 23, 2010 at 9:29 am
Lutz
I am trying to replace the carriage returns with <br /> to display in html.
Increasing the size of the results text now gives me the desired results for the select command. Thanks for that It never even occured to me.
I am actually tring to run this as an update on a table, therefore I expect to return the original data in full with the replaced text as well. I am getting the same truncated data in my table.
February 23, 2010 at 9:37 am
Lutz
OK so I now have to eat my hat!
The results were correct as I imagined they should be. The results were incorrcet as SSMS was truncating the data I was checking.
Thank you very much.
February 23, 2010 at 9:39 am
Would you please provide the script you're using to update your table together with some sample data that will show the effect you get?
February 23, 2010 at 10:22 am
My last post didn't recognize your latest reply. So please ignore it.
Glad it finally worked out. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply