How to pass large string_replacement in SQL Replac

问题:

I am attempting to replace a very large characters with a existing character in SQL, like the below

select REPLACE('main context', 'text', 'CharactersOver8000')

It would throw the following error

String or binary data would be truncated.

I try to cast the whole replace to nvarchar(max) but it does not work. Any suggestion? Currently its on SQL2012

回答1:

What is the cause of this error message?

---

From the Docs Online

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.

---

What is the solution?

---

string_expression in your case is 'MainContext', so you need to cast it to VARCHAR(MAX) or NVARCHAR(MAX) datatypes as

select REPLACE(CAST('main context' AS VARCHAR(MAX), 'text', 'CharactersOver8000')

回答2:

Casting inside the replace should work. I just tested this:

SELECT REPLACE(CAST('MainContext' AS varchar(max)), 'text', '{8005 character string}')

And it worked.