Search NVARCHAR(MAX) string in other string I want to find the index of a string in other string. I try to use CHARINDEX and PATINDEX but nothing worked because the search string is NVARCHAR(MAX) and these functions are limited to 8000 chars only.

I am getting the following error:

Msg 8152, Level 16, State 10, Line 13
String or binary data would be truncated.


Are there any alternatives?

The goal is to replace the find string with other, but REPLACE is not working with NVARCHAR(MAX) either. That's why I am trying to use .WRITE clause but need to calculate the offset.

This is sample code. Basically, I am trying to replace the source of img HTML tags from BASE64 to relative url.

DECLARE @DataSource TABLE
(
    [Content] NVARCHAR(MAX)
)

DECLARE @defaultStartScreenOld nvarchar(max) = N'a NVARCHAR(MAX) big image in BASE64 format';
DECLARE @defaultStartScreenNew nvarchar(2000) = N'images/surveo/presetImages/Thumb_PointerStart.jpg';

INSERT INTO @DataSource ([Content])
SELECT CONCAT('&lt;RUSLAN src="', @defaultStartScreenOld, '"/&gt;')

SELECT [Content]
FROM @DataSource

UPDATE @DataSource
--SET [Content].WRITE(@defaultStartScreenNew, 10, LEN(@defaultStartScreenNew))
SET [Content] = REPLACE([Content], @defaultStartScreenOld, @defaultStartScreenNew)

SELECT [Content]
FROM @DataSource
Considering that:


REPLACE, PATINDEX, CHARINDEX and even LIKE have not been updated to fully and properly deal with LOB/MAX types; and,
you need to repeat this process across hundreds of databases and against multiple instances


I would strongly recommend you look at doing this outside of SQL Server, probably in PowerShell or C# (not CLR inside SQL Server, which will be prone to similar data type limitations and restrictions). You can write a simple C# command line program that pulls in each row, performs the replace inline, and then updates the row wholesale. Then you can point the C# app at each instance/database combination. PowerShell may have some limitations with handling huge chunks of data like this, but I recommend looking into that as well simply due to the automation factor (you can simply feed it a text file of instance/database names and it will churn through all of them in one shot). Even if you could trust the CLR approach to do this correctly, I think the debugging effort will be higher, and you'll also have to deploy the assemblies everywhere (vs. a single app or script that can just be pointed at each instance in turn).

I'm not sure I'm the one you want giving specific guidance on the code you would write using either of these approaches, I just want to steer you away from trying to solve this in T-SQL, since the deck is kind of stacked against you there.