Replacing a string using SQL Server Replace function - string has more than 4000 characters - what to do?

Question

I have a table, where I need to replace some values in a column.

The database is running on SQL Server 2005.

The problem is that some of the rows contain more than 4000 characters, which is giving the REPLACE function some trouble, since it demands that I cast the first parameter to the datatype NVARCHAR, and therefore any characters exceeding 4000, is being truncated.

Is there any workaround for this, other than writing an application that handles this issue?

The query in question is:

SELECT 
   Replace(cast([Database].[dbo].[fruits].[Tekst] as NVARCHAR(MAX)), 'bananas', 'apples') 
FROM [Database].[dbo].[fruits]

The column fruits is of datatype Text

Any input appreciated.

Why would nvarchar(max) cause truncation at 4000 chars? What does this return for you? SELECT LEN(REPLACE(REPLICATE(CAST('A' AS NVARCHAR(MAX)),16000),'A','B')) Are you sure your issue isn't simply that it is getting truncated in the SSMS results? — Martin Smith
– Martin Smith, Commented Mar 9, 2011 at 12:31
Because the maximumvalue of nvarchar is 4000: msdn.microsoft.com/en-us/library/ms186939.aspx. The query you gave me returns the value 16000, but how can I make it iterate through a column to test for values? Replacing 'A' inside the cast with the column didn't solve the issue.. — MadsMadsDk
– MadsMadsDk, Commented Mar 9, 2011 at 12:47
@MadsMadsDk - No 4000 is the maximum explicit value. Using max allows it to hold up to 2GB of data (a billion double byte characters) — Martin Smith
– Martin Smith, Commented Mar 9, 2011 at 12:50
@madsmadsdk: the maximum for NVARCHAR(MAX) is 2 GByte of data - roughly 250 times the volume of Tolstoj's War and Peace - enough for you!?!??! — marc_s
– marc_s, Commented Mar 9, 2011 at 13:03

Martin Smith · Accepted Answer · 2011-03-09 13:00:37Z

5

I suspect that you have just hit the limit for an individual value in SSMS.

Annoyingly it doesn't allow you to set this to be unlimited and the only way I know of displaying long text is via casting to XML as below.

select
(select Replace(cast([Database].[dbo].[fruits].[Tekst] as NVARCHAR(MAX)),'bananas','apples')  AS [processing-instruction(x)] FOR XML PATH(''), TYPE)
FROM [Database].[dbo].[fruits]

answered Mar 9, 2011 at 13:00

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbn · Accepted Answer · 2011-03-09 12:45:23Z

This demonstrates how REPLACE can handle longer string

SELECT CAST(REPLICATE(N'abc', 4000) AS nvarchar(MAX)) +
               REPLICATE(N'def', 4000)  +
               REPLICATE(N'abc', 4000)

SELECT LEN(
       CAST(REPLICATE(N'abc', 4000) AS nvarchar(MAX)) +
               REPLICATE(N'def', 4000)  +
               REPLICATE(N'abc', 4000)
       ) --11997

SELECT REPLACE(CAST(REPLICATE(N'abc', 4000) AS nvarchar(MAX)) +
               REPLICATE(N'def', 4000)  +
               REPLICATE(N'abc', 4000), 'def', 'ddeeff')

SELECT LEN(
       REPLACE(CAST(REPLICATE(N'abc', 4000) AS nvarchar(MAX)) +
               REPLICATE(N'def', 4000)  +
               REPLICATE(N'abc', 4000), 'def', 'ddeeff')
       ) --15996

Collectives™ on Stack Overflow

Replacing a string using SQL Server Replace function - string has more than 4000 characters - what to do?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related