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.

Solution 1

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]

Solution 2

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