Search and replace part of string in database

SqlSql ServerTsql

Sql Problem Overview

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%'

Say I want to replace the following code segment:

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

With this:

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

Sql Solutions

Solution 1 - Sql

You can do it with an UPDATE statement setting the value with a REPLACE

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

You will want to be extremely careful when doing this! I highly recommend doing a backup first.

Solution 2 - Sql

I think 2 update calls should do

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

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

Solution 3 - Sql

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

and you do it in a single pass.

Solution 4 - Sql

I was just faced with a similar problem. I exported the contents of the db into one sql file and used TextEdit to find and replace everything I needed. Simplicity ftw!

Solution 5 - Sql

I would consider writing a CLR replace function with RegEx support for this kind of string manipulation.

Solution 6 - Sql

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

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions

Previous Solution:

Command line tool to dump Windows DLL version?

DllCommand LineManifestVersioninfo

Next Solution:

Common Ruby Idioms

RubyIdioms

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionZookingView Question on Stackoverflow
Solution 1 - SqlMufakaView Answer on Stackoverflow
Solution 2 - SqlkristofView Answer on Stackoverflow
Solution 3 - SqlSFAView Answer on Stackoverflow
Solution 4 - SqlWestleyView Answer on Stackoverflow
Solution 5 - SqlManuView Answer on Stackoverflow
Solution 6 - SqlVijay Balkrishna konduskarView Answer on Stackoverflow