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_storiesset 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]