• Tweet

• Tweet

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:

cast(ntext_field as nvarchar(max))

or:

cast(text_field as varchar(max))

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

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

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

update tbl_stories 
set story = REPLACE(cast(story as nvarchar(max)), 'string_search', 'string_replace');
-- where condition

update tbl_stories 
set description = REPLACE(cast(description as varchar(max)), 'string_search', 'string_replace');
-- where condition

Some examples for Replace() function

declare @message varchar(50)
set @message = 'I am a web designer'
select REPLACE(@message,'designer', 'developer')

I am a web developer

select REPLACE('Do you like MSSQL?','MSSQL', 'SQL Server')

Do you like SQL Server?