x

How do I handle REPLACE() within an NTEXT column in SQL Server?

How do I handle REPLACE() within an NTEXT column in SQL Server?

more ▼

asked Nov 13 '09 at 10:15 AM in Default

Anthony Osborn gravatar image

Anthony Osborn ♦♦
171 7 9 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Well, if you know the position of the text you want to replace you can use UpdateText. Or you can convert to nvarchar, use Replace and then use UpdateText to replace the entire NTEXT column data.

more ▼

answered Nov 13 '09 at 11:21 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

As you have no doubt discovered , REPLACE will not work on a NTEXT column. You could create your own , fairly crude, version using PATINDEX. Which version of SQLServer are you running ?

more ▼

answered Nov 13 '09 at 11:19 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

(comments are locked)
10|1200 characters needed characters left

UPDATE Table1 Set Field1 = convert(ntext, replace(convert(nvarchar(max), Field1), 'Find', 'Replace')) where Field1 like '%Find%'

more ▼

answered Mar 12 '10 at 03:22 PM

paul 1 gravatar image

paul 1
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x18
x2

asked: Nov 13 '09 at 10:15 AM

Seen: 4372 times

Last Updated: Nov 13 '09 at 10:15 AM