[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3992
  • Last Modified:

SQL Replace nText with more than 8000 characters

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
guswilliams
Asked:
guswilliams
  • 4
  • 4
  • 2
  • +1
1 Solution
 
AdamSenior DeveloperCommented:
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
 
guswilliamsAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
guswilliamsAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Also, as far as I recall if body is text or ntext you cannot do this:
WHERE body = N'test'
0
 
guswilliamsAuthor Commented:
My query is actually Where body like '%test%'
0
 
Anthony PerkinsCommented:
Perhaps you can show us the before and after (obfuscated) data so that we get a better idea of what is happening.
0
 
guswilliamsAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
AdamSenior DeveloperCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now