Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server Replace String With NText Or Text Data Type In MS SQL Server

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]
Nov 3, 2011Hoan Huynh
ASP.NET C# Export Data To CSV And Prompt DownloadPHP Connect To MS SQL Server
You Might Also Like:
  • Replace String In MySQL
  • MSSQL Split String Function
  • SQL SERVER How to import Text/CSV file into table
  • ASP.NET C# Export Data To CSV And Prompt Download
  • MSSQL Trim Function
  • MySQL Incorrect Data Type Value In Column
  • Javascript Problem Set focus textbox on Firefox
  • MSSQL Split Function returns table data
  • PHP Remove All Special Characters And Replace Spaces With Hyphens
  • SQL Server Get number of working days and business hours between start date and end date
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

9 years ago SQL Servercast, ntext, nvarchar, replace, varchar708
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
22,485 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
20,284 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
16,043 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,519 views
C# Read Json From URL And Parse/Deserialize Json
10,067 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Online Payday Loans – Learn How To Make the Most of A Alternative Lending Option
  • Strategies For Buying Photo Editor Software
  • Where to Find the Greatest Free Photo Editor on the Web
  • Custom Research Paper – What’s it So Useful?

  • Getting Bad Credit Paydayloans From a Reputable Source
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (247)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development