MSSQL error: the parameter data type text is invalid for parameter 1 of the replace function. Solution

Time:2021-12-25

But we can solve this problem in another way. Let’s analyze the solution.

String operations cannot be performed on data of type text or ntext in a query. At this time, the most commonly used is to treat text as varchar (when the actual content length is less than 8000 bytes) or ntext as nvarchar (when the actual content length is less than 4000 bytes). However, this is not very appropriate. After all, if the text field content exceeds 8000 bytes, it will not be truncated or ignored.
Finally, a method is found to solve the problem that the parameter data type text is invalid for parameter 1 of the replace function. Here is an example to illustrate how to deal with it.
The syntax is as follows:

Copy codeThe code is as follows:
update table set column=replace(cast(column as varchar(max)),’123′,’abc’)

Explanation:
The table represents the table name, and the column code represents the column in the table that needs to be replaced. The function of the change statement is to replace all occurrences of 123 in the column column of the table with ABC.
Attachment: introduction of Max type
The max specifier was introduced in Microsoft SQL Server 2005. This specifier enhances the storage capacity of varchar, nvarchar, and varbinary data types. Varchar (max), nvarchar (max), and varbinary (max) are collectively referred to as large value data types. You can use the large value data type to store data up to 2 ^ 31-1 bytes.
be careful:
When sp_ When the ‘large value types out of row’ option of the tableoption stored procedure is set to off, the in row storage of large value types is limited to 8000 bytes. When this option is set to on, only 16 byte roots are stored in the row. For more information, see sp_ tableoption (Transact-SQL)。
Large value data types behave similarly to their corresponding smaller data types varchar, nvarchar, and varbinary. This similarity enables SQL server to store and retrieve large character, Unicode, and binary data more efficiently.
With large value data types, it is impossible to use SQL server by using text, ntext and image data types in earlier versions of SQL server. For example, in SQL Server 2005, you can define variables that can store large amounts of data (up to 2 ^ 31 bytes of character, integer, and Unicode data). For more information, see transact SQL variables.
The following table describes the relationship between large value data types and their corresponding data types in previous versions of SQL server.

Large value data type Lob in previous releases
varchar(max) text*
nvarchar(max) ntext*
varbinary(max)

image

*SQL Server {6.5 clients do not support ntext data type, so nvarchar (max) is not recognized.

Important:
Use varchar (max), nvarchar (max), and varbinary (max) data types instead of text, ntext, and image data types.
Large value data types behave the same as their corresponding smaller data types varchar (n), nvarchar (n), and varbinary (n). The following describes the use of large value data types in some specific situations:
Because the cursor can define a large value data type variable, it can assign the data in the large value data type column returned by fetch to the local variable. For more information, see fetch (transact SQL). The use of large value data types does not affect the forced cursor type conversion usage of cursors.
Block update statements are now supported The write () clause partially updates the underlying large value data column. This is similar to the text pointer operations, writetext, and updatetex for text, ntext, and image data types supported in previous versions of SQL server. For more information, see update (transact SQL). Triggers support the use of after triggers on large value data type column references in inserted and deleted tables. For more information, see create trigger (transact SQL).
The built-in string functions that manipulate characters and binary data are enhanced to support parameters of large value data types. These functions include:

Copy codeThe code is as follows:
COL_LENGTH
CHARINDEX
PATINDEX
LEN
DATALENGTH
SUBSTRING