Replacing a character

Hi

I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given character\string with another one in this column. Any assistance on this is highly appreciated.

Thanks!

Santhosh

Santhosh,

1) If the text in the column is not too long, you can cast ntext to nvarchar and use replace function.

2) check this link http://www.webpowersoftware.co.uk/FORUMS/ShowPost.aspx?PostID=336

rgds,

v r kumar

Replace and ntext

I need replace a string in a ntext field.
Any ideas ?
Tks.check following example on pubs database . This will create a new table
pub_info_1 with the replaced data of pub_info table. Following example will
work for TEXT datatype for NTEXT, probably you will have to take care of
datalength which is generally datalength/2 (because unicode takes 2 bytes to
store a character.)
(Take a copy of the actual table before trying anything.)
DECLARE @.orig_str varchar(8000), @.rep_str varchar(8000)
SET NOCOUNT ON
DECLARE @.y int,@.str varchar(8000),@.dtlen int, @.pub_id int
DECLARE @.ptrval binary(16),@.ptrval1 binary(16)
SELECT @.orig_str='new moon books', --old string
@.rep_str='old moon books' --new string
--rep_str should not be greater than 100
SELECT @.pub_id = 0
IF object_id('pub_info_1') is not null
DROP table pub_info_1
CREATE table pub_info_1 (new_txt text)
if object_id('tempdb..#txt_1') is not null
drop table #txt_1
CREATE table #txt_1 (xx text)
WHILE @.pub_id is not null
BEGIN
TRUNCATE table #txt_1
INSERT into #txt_1 values ('')
SELECT @.ptrval1 = TEXTPTR(xx)
FROM #txt_1
SELECT @.pub_id=min(pub_id)
FROM pub_info where pub_id > @.pub_id
-- FROM pub_info where pub_id = '9999'
SELECT @.ptrval = TEXTPTR(pr_info) , @.y=1, @.dtlen = datalength(pr_info)
FROM pub_info where pub_id = @.pub_id
WHILE 1=1
BEGIN
SELECT @.str= replace (substring(pr_info, @.y, 7900), @.orig_str, @.rep_str)
FROM pub_info where pub_id = @.pub_id
UPDATETEXT #txt_1.xx @.ptrval1 null 0 @.str
SELECT @.y = @.y + 7900
IF @.y > @.dtlen
BEGIN
BREAK
END
END
If @.pub_id is not null
INSERT into dbo.pub_info_1
SELECT * FROM #txt_1
END
DROP TABLE #txt_1
--
-Vishal
SJPiola <sjpiola@.msn.com> wrote in message
news:029401c34fc0$8f102160$a401280a@.phx.gbl...
> I need replace a string in a ntext field.
> Any ideas ?
> Tks.

Replace a string in an NTEXT field in sql server

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

NTEXT is depricated in SQL 2005 and was replaced by NVARCHAR(MAX). You can use the REPLACE in SQL 2005

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

|||Can access a sql2000 table with NTEXT field as it is and replace a string in that field using SSIS2005? Please advise. Thanks.|||

As I know in SQL2000 the REPLACE will not accept NTEXT data as parameter, which brings trouble when update TEXT data. If there are less than 4000 chars in the NTEXT column, I'd suggest casting the NTEXT data to NVARCHAR(4000) data so that you can use the REPLACE function. Otherwise the replacing is really ugly. Here is a sample to update TEXT data by replacing string:

USE TempDB;
GO

SET NOCOUNT ON;

CREATE TABLE dbo.data
(
DataID INT PRIMARY KEY,
txt NTEXT -- change to TEXT
);
GO

INSERT dbo.data
SELECT 1, N'bar foodfood food har sammy'
UNION ALL SELECT 2, N'bar sammy food'
UNION ALL SELECT 3, N'bar fooblat sammy'
UNION ALL SELECT 4, N'food';

DECLARE
@.TextPointer BINARY(16),
@.TextIndex INT,
@.oldString NVARCHAR(32), -- change to VARCHAR
@.newString NVARCHAR(32), -- change to VARCHAR
@.lenOldString INT,
@.currentDataID INT;

SET @.oldString = N'food'; -- remove N
SET @.newString = N'fudge'; -- remove N

IF CHARINDEX(@.oldString, @.newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN
SELECT 'Before replacement:';

SELECT DataID, txt FROM data;

SET @.lenOldString = DATALENGTH(@.oldString)/2; -- remove /2

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
DataID
FROM
dbo.data
WHERE
PATINDEX('%'+@.oldString+'%', txt) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @.currentDataID;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

SELECT
@.TextPointer = TEXTPTR(txt),
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID;

WHILE
(
SELECT
PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID
) > 0
BEGIN
SELECT
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)-1
FROM
dbo.data
WHERE
DataID = @.currentDataID;

UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
END

FETCH NEXT FROM irows INTO @.currentDataID;
END

CLOSE irows;

DEALLOCATE irows;

SELECT 'After replacement:';

SELECT DataID, txt FROM data;
END

DROP TABLE dbo.data;

|||

Does your solution apply only if there are less than 4000 chars in the NTEXT column? Here's my next questions: how can I find the max length of those ntext fields? But if the max length exceeds 4000, can I use nvarchar(max) in the sql2005 temp table and, after replacing my string, convert the result back to ntext in the sql 2000 source table? Can I use sql2005 for temp work and keep the final data in sql2000. Thanks.

|||You can use datalength(yourColumn) to find your ntext length.|||lori_Jay's solution looks good but has a serious flaw: the new string replacement is truncated to the old string's length after it goes into the NTEXT field. Please advise. Thanks.|||

Problem can be solved with repalcing:
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
with
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString -- deletes old string
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex 0 @.newString -- inserts new string