How To Use Replace function in TEXT and NTEXT fields

How To Use Replace function in TEXT and NTEXT fields

Replace function in T-SQL could be very useful. As its name implies, Replace function is used to search and replace some text. But, problem occurs if you try to use it with TEXTor NTEXT data types. In this case, SQL Server will return an error:

Error Message: Argument data type text is invalid for argument 1 or replace function.

This unfriendly message says that we have a problem, but doesn't say anything about how to solve it. Since Replace function is supported for VARCHAR and NVARCHAR data types, an easy solution is to convert TEXT or NTEXT data to VARCHAR and NVARCCHAR respectively.

Let say you have table Articles with column ArticleText which is of type NTEXT. Using of Replace function in example T-SQL code could look like this:

SELECT REPLACE(CAST(ArticleText AS NVARCHAR(MAX)),'Existing text','New text')
FROM Articles

This query is simply converted NTEXT data to NVARCHAR(MAX) and then used it in Replace function. Sometimes, you need to your query returns original data type. In this example, original data type is NTEXT. To get it back after replacing, we can use CAST function again. So, example would be a little bigger, like this:

SELECT CAST(REPLACE(CAST(ArticleText AS NVARCHAR(MAX)),'Existing text','New Text') AS NTEXT)
FROM Articles

T-SQL Replace function remarks

NVARCHAR(MAX), VARCHAR(MAX) and BINARY(MAX) are introduced with SQL Server 2005. They are created as replacement for TEXT, NTEXT and BINARY data types. As Microsoft said, TEXT, NTEXT and BINARY are obsolete and will be removed from future versions of SQL Server. Instead of using CAST function like in examples above, you can consider to redesign tables and change TEXT and NTEXT data types to new VARCHAR(MAX) and NVARCHAR(MAX).

Then you can use REPLACE or other function directly, without data conversion.

Also remember, there is always an option to do search/replace in application code by using C# or VB.NET

Happy coding!