Modifying selected text within a text column

Background

while working on a web project, i had to work with a large amount of text stored in a single column of a table, search for a pattern such as "code project" and replace it with another text such as "Code Project Article" or "Code Project Inc" depending on the requirement.

i was new to SQL Server at that time and had to search for an answer for a long time before i could figure it out.

i thought my experience could help someone like me who are new to SQL Server. so i'm including my approach and code here.

Using the code

The code i'm including here can be run in SQL Server query analyzer or stored as a stored procedure or used in anyway as a regular SQL Script.

in this script, i'm fetching data from a column called "targetcolumn" from table "table1".

this column has datatype "text". this column contains many thousands of characters.

i'm using a cursor here so that multiple rows can be updated if necessary

the old text here is otxt=code project.

new text that replaces it is ntxt = "Code Project Article"

    

BEGIN TRY




    BEGIN TRAN




declare @otxt varchar(50)




set @otxt = 'code project'

declare @ntxt varchar(1000)


 

set @ntxt = 'Code Project Article"

declare @txtlen int


 

set @txtlen = len(@otxt)


declare curs cursor 


 for


SELECT


 

dbo.table.targetcolumn, 

textptr(dbo.table.targetcolumn),


 

charindex(@otxt, dbo.table.targetcolumn)-1 

 FROM  dbo.table1 INNER JOIN


 

   dbo.table2 ON ......
 

WHERE     (.......) 
ORDER BY dbo.table1.column1

 

 

 

declare @ptr binary(16)

declare @pos int


 

declare @MainContent varchar

open curs


 

 

fetch next from curs into @MainContent, @ptr, @pos

while @@fetch_status = 0


 

begin    

 

 updatetext  <a href="mailto:dbo.table1.tergetcolumn@ptr">dbo.table1.tergetcolumn@ptr</a> @pos @txtlen @ntxt 

 

 

   fetch next from curs into @MainContent, @ptr, @pos       

 

end

COMMIT TRAN


END TRY


 

BEGIN CATCH

 ROLLBACK TRAN


 

 PRINT 'Transaction rolled back'

 

END CATCH









        

The Try Catch block is available only in SQL Server2005. SQL Server 2000 does not include this feature.

Points of Interest