Search and replace part of a string in a database

Question

I need to replace all iframe tags stored as nvarchar in my database. I can find the entries using the following sql question:

SELECT * FROM databasename..VersionedFields WHERE Value LIKE '%<iframe%' 

Let's say I want to replace the following code segment:

 code before iframe <iframe src="yadayada"> </iframe> code after iframe 

Wherein:

 code before iframe <a>iframe src="yadayada"</a> code after iframe 

Answer 1

I think 2 update calls should do

 update VersionedFields set Value = replace(value,'<iframe','<a><iframe') update VersionedFields set Value = replace(value,'> </iframe>','</a>') 

Answer 2

You can do this with an UPDATE statement that sets a value using REPLACE

 UPDATE Table SET Column = Replace(Column, 'find value', 'replacement value') WHERE xxx 

You will be very careful with this! I highly recommend backing up first.

Answer 3

 update VersionedFields set Value = replace(replace(value,'<iframe','<a>iframe'), '> </iframe>','</a>') 

and you do it in one go.

Answer 4

I ran into a similar problem. I exported the contents of db to a single sql file and used TextEdit to find and replace everything I need. Simplicity ftw!

Answer 5

I would consider writing a CLR replacement function using RegEx for this kind of string manipulation.

Answer 6

Update the database and Set fieldName = Replace (fieldName, 'FindString', 'ReplaceString')