Update text in table column with ntext datatype

• ez689

  SSC Enthusiast

  Points: 100

  More actions

  January 12, 2008 at 3:40 am

  #69697

  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

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  January 12, 2008 at 5:07 am

  #767404

  SQL Server is not a text editor.

  It's Relational Database Management System.

  You have to choose:

  - either to store you data in proper relational database and use RDMS to manage it;

  - or keep storing it as a text file and find another, proper tool to edit it.

  _____________
  Code for TallyGenerator

• David.Poole

  SSC Guru

  Points: 76221

  More actions

  January 12, 2008 at 9:02 am

  #767412

  If the length of your ntext field is under 4000 characters then cast it to a VARCHAR and use the REPLACE statement.

  LinkedIn Profile