Update text in table column with ntext datatype

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update text in table column with ntext datatype

Author

Topic

ez689@live.com
Starting Member

2 Posts

Posted - 2008-01-12 : 05:11:21

need help on how to update a string in a table column. I want to replace 'Brodmann-10' with 'Brodmann-1910'. The following script does not work right. I appreciate your help

DECLARE @FindString varchar(80)
DECLARE @ReplaceString varchar(80)

SET @FindString = 'Brodmann-10'
SET @ReplaceString = 'Broadmann-1910'

SET NOCOUNT ON

DECLARE @TextPointer varbinary( 16 )
DECLARE @DeleteLength int
DECLARE @OffSet int

SELECT @TextPointer = textptr( Description)
FROM Brodmann --Brodmann is a table only has 5 rows
SET @DeleteLength = len( @FindString )
SET @OffSet = 0
WHILE ( SELECT count( * ) FROM Brodmann WHERE charindex( '%Brodmann-10%', Description) <> 0) > 0
BEGIN
SELECT @OffSet = charindex( '%Brodmann-10%', Description) -1
FROM Brodmann
WHERE charindex('%Brodmann-10%', Description) <> 0
UPDATETEXT Brodmann.Description
@TextPointer
@OffSet
@DeleteLength
@ReplaceString
END
RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 06:58:58

Change like this & try:-

DECLARE @FindString varchar(80)
DECLARE @ReplaceString varchar(80)

SET @FindString = 'Brodmann-10'
SET @ReplaceString = 'Broadmann-1910'

SET NOCOUNT ON

DECLARE @TextPointer varbinary( 16 ) 
DECLARE @DeleteLength int 
DECLARE @OffSet int
DECLARE @ID int

SET @DeleteLength = len( @FindString ) 
SET @OffSet = 0


SELECT @ID=MIN(PKCol)
FROM Brodmann
WHERE charindex('%Brodmann-10%', Description) <> 0

WHILE @ID IS NOT NULL
BEGIN 
SELECT @TextPointer = textptr( Description)
FROM Brodmann --Brodmann is a table only has 5 rows
WHERE PKCol=@ID

SELECT @OffSet = charindex( '%Brodmann-10%', Description) -1
FROM Brodmann
WHERE PKCol=@ID

UPDATETEXT Brodmann.Description
@TextPointer
@OffSet
@DeleteLength
@ReplaceString

SELECT @ID=MIN(PKCol)
FROM Brodmann
WHERE charindex('%Brodmann-10%', Description) <> 0
AND PKCol>@ID

END
RETURN

Where PKCol is the primary key column of Brodmann table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources