Replace String With NText Or Text Data Type In MS SQL Server

In MS SQL Server, there is a function called Replace which will replace all occurrences of a specified string value with another string value. And we can use this function in an Update SQL statement to change value of a particular column. However, if we replace a string in a ntext or text field, Replace() function doesn’t work and returns an error message: “Argument data type ntext is invalid for argument 1 of replace function.” or “Argument data type text is invalid for argument 1 of replace function.”.

In order to work out this issue, we may need to convert the ntext/text field to nvarchar/varchar respectively. As long as the ntext/text field is not too large, we can use:

[sql] cast(ntext_field as nvarchar(max))
[/sql]

or:

[sql] cast(text_field as varchar(max))
[/sql]

Assume we have a table called tbl_stories in a SQL Server with structure as below:

[sql highlight=”4,5″] CREATE TABLE [dbo].[tbl_stories](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](50) NULL,
[story] [ntext] NULL,
[description] text NULL,
[created_date] [datetime] NULL,
CONSTRAINT [PK_tbl_stories] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_stories] ADD CONSTRAINT [DF_tbl_stories_created_date] DEFAULT (getdate()) FOR [created_date] GO
[/sql]

In order to replace value for column story (ntext) and description (text), follow a simple SQL below:

[sql] update tbl_stories
set story = REPLACE(cast(story as nvarchar(max)), ‘string_search’, ‘string_replace’);
— where condition
[/sql] [sql] update tbl_stories
set description = REPLACE(cast(description as varchar(max)), ‘string_search’, ‘string_replace’);
— where condition
[/sql]

Some examples for Replace() function

[sql] declare @message varchar(50)
set @message = ‘I am a web designer’
select REPLACE(@message,’designer’, ‘developer’)
[/sql] [text] I am a web developer
[/text]

---

[sql] select REPLACE(‘Do you like MSSQL?’,’MSSQL’, ‘SQL Server’)
[/sql] [text] Do you like SQL Server?
[/text]