Solved

SQL Replace nText with more than 8000 characters

Posted on 2013-02-05
11
3,542 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

679 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