Solved

SQL Replace nText with more than 8000 characters

Posted on 2013-02-05
11
3,210 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:ScottPletcher
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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now