Solved

SQL Replace nText with more than 8000 characters

Posted on 2013-02-05
11
3,384 Views
Last Modified: 2013-02-12
How do I run a SQL Replace query on columns that are nText with more than 8000 characters?

If I run this
  UPDATE Articles SET Body = REPLACE(Body,'test','test2')
where body = 'test'

I get this error:
Argument data type ntext is invalid for argument 1 of replace function.

But if I run this
UPDATE Articles SET Body = CAST(REPLACE(CAST(Body as varchar(MAX)),'test','test2') AS NText)
where body = 'test'

It works but it truncates the data.  

How do I get around it.

I'm running SQL 2008 and I've also tried it on SQL 2012
0
Comment
Question by:guswilliams
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 12

Accepted Solution

by:
Cyber-spy earned 500 total points
ID: 38857708
I just created a test table called Articles, with an nText column called Body, and populated it with a string over 23,000 chars long

I then ran your last query

UPDATE Articles SET Body = CAST(REPLACE(CAST(Body as varchar(MAX)),'test','test2') AS NText)

followed by

select * from Articles

I copied and pasted the result from this into a text editor and it was all still there, no truncation.

This was with SQL Server 2012, and you used the method suggested by this article - http://msdn.microsoft.com/en-us/library/ms186862(v=sql.100).aspx
which says:
If string_expression is not of type varchar(max) or nvarchar(max),REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

This is also stated for SQL Server 2008R2 (you can chnage the server version at the top of the page) but not SQL Server 2008
0
 

Author Comment

by:guswilliams
ID: 38860026
I ran it again and it seems to be only a couple that are being truncated.

One record has 43495 Chars and after I run the update it still has 43495 Chars, but the last 72 chars have been truncated.

My replace statement is replacing 10 Chars with 48 Chars.

Any ideas?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38860210
Since your column is ntext, I would certainly use nvarchar instead of varchar, to avoid any possible conversion issues there.

UPDATE Articles
SET Body = CAST(REPLACE(CAST(Body AS nvarchar(MAX)),N'test',N'test2') AS ntext)
WHERE body = N'test'

And as soon as you can, change the column itself to nvarchar(max) and avoid all this hassle.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:guswilliams
ID: 38860386
Ran you above query
UPDATE Articles
SET Body = CAST(REPLACE(CAST(Body AS nvarchar(MAX)),N'test',N'test2') AS ntext)
WHERE body = N'test'

Still same results on the column with 43495 Chars
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38863895
Still same results on the column with 43495 Chars
How is that possible?  body = "test" and has to be 4 characters.

What am I missing?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38863963
Also, as far as I recall if body is text or ntext you cannot do this:
WHERE body = N'test'
0
 

Author Comment

by:guswilliams
ID: 38864345
My query is actually Where body like '%test%'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38864791
Perhaps you can show us the before and after (obfuscated) data so that we get a better idea of what is happening.
0
 

Author Comment

by:guswilliams
ID: 38864847
Actual update statement
UPDATE Articles SET Body = CAST(REPLACE(CAST(Body as nvarchar(MAX)),'attendance','<a href=''http://www.website.com''>attendance</a>') AS NText)
where body like '%attendance%'

Here is the last line in the field before running the update.

<P style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal" classx="MsoNormal"><B style="mso-bidi-font-weight: normal"><XSPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Approved</XSPAN></B><XSPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"> a</XSPAN><XSPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Arial','sans-serif'; mso-fareast-font-family: 'Times New Roman'"> request by ten local educational agencies (LEA) to waive the State Testing Apportionment Information Report deadline of December 31st in the <I>California Code of Regulations (CCR)</I>, Title 5, Section 11517.5(b)(1)(A) regarding the California English

Here it is after.

<P style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal" classx="MsoNormal"><B style="mso-bidi-font-weight: normal"><XSPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Approved</XSPAN></B><XSPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"> a</XSPAN><XSPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Arial','sans-serif'; mso-fareast-font-family: 'Times New Roman'"> request by ten local educational agencies (LEA) to waive the State Testing Apportionment Information Report deadline of December 31st in the <I>California Code of Regulations (CCR)<
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38865448
I suspect this is a limitation of what you are seeing using SSMS (can be changed using Tools|Options) and not with the actual data.  This can be confirmed by checking the length of the data using DATALENGTH() and not LEN() )
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 38882185
SSMS, by default displays 66535 characters in GRID view and 256 in Text view

This can be changed in Tools -> Options -> Query Results -> SQL Server -> Results to Grid or Results to Text
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
Convert formula to max at a specific date by month 18 22
Merge two rows in SQL 4 17
Row_number in SQL 5 13
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question