Editing a column of text data type

• sql_fan

  SSC-Addicted

  Points: 462

  More actions

  March 9, 2006 at 1:24 pm

  #112227

  I have  a database table and one of its column is called case_Details of data type text. I need to modify the content of this column for one of the record.

   

  I have 1200 records and the table has an identity column(ID1) and I need to modify the content of the row#954(which is called ID1).

   

  How can I do this.

   

  Thanks.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  March 9, 2006 at 1:36 pm

  #625793

  What exactly you did not understand in BOL article "UPDATETEXT"?

  _____________
  Code for TallyGenerator

• sql_fan

  SSC-Addicted

  Points: 462

  More actions

  March 13, 2006 at 8:46 am

  #626275

  UPDATETEXT will update the part of the text but I want something different.

  say my text column has a string 'abcdefghijklmnopqrstuvwxyz'

  I need  add 'STIRNGCHANGE' in place of 'fgh' of the original string.

  So modified string(of data type text) should be like this.

   

  'abcdeSTIRNGCHANGEijklmnopqrstuvwxyz'

   

  Thanks.

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 13, 2006 at 9:37 am

  #626291

  Actually updatetext does just that "exactly"

  declare @startIndex int, @patlength int, @prtval varbinary(16)

  set @patLength = 3 --len('fgh')

   

  SELECT @prtval  = TEXTPTR(case_Details) , @startIndex  = PATINDEX('%fgh%', case_Details)

  FROM yourtable

  WHERE pk_id = 1234  -- Use your primary key

       and PATINDEX('%fgh%', case_Details) > 0  -- make sure it exists

  if @@rowcount = 1

   updatetext yourtable.case_Details, @prtval  @startIndex  @patLength 'STIRNGCHANGE'

  good luck

   

  
  * Noel