Click here to Skip to main content
Click here to Skip to main content

Tagged as

(untagged)

Modifying selected text within a text column

, 13 Dec 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
searching for a pattern such as "code project" within the text in a column and replacing it with another text such as "Code Project Article"

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vijaya Pulugurta

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.141029.1 | Last Updated 13 Dec 2007
Article Copyright 2007 by Vijaya Pulugurta
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid