[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Replace nText with more than 8000 characters

Posted on 2013-02-05
11
Medium Priority
?
3,899 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 12

Accepted Solution

by:
Adam earned 2000 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 70

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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:Adam
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

656 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