Thursday, August 26, 2004

Replace content in database table (SQL)

You may have rows in your database that contains <br>, which you want to replace with line breaks (this may be the case if you are using Server.HtmlEncode to encode your output, which you didn't previously do). This can simply be done using the replace function (note the line break):

UPDATE [table]
SET
  [column]= REPLACE([column], '<br>', '
')
WHERE
  [column] LIKE '%<br>%'
Also, if you wish to convert < and > to their html encoded equivalents:
UPDATE [table]
SET
  [column]= REPLACE([column], '<', '&lt;')
WHERE
  [column] LIKE '%<%'
UPDATE [table]
SET
  [column]= REPLACE([column], '>', '&gt;')
WHERE
  [column] LIKE '%>%'
Or to do the reverse:
UPDATE [table]
SET
  [column]= REPLACE([column], '&lt;', '<')
WHERE
  [column] LIKE '%&lt;%'
UPDATE [table]
SET
  [column]= REPLACE([column], '&gt;', '>')
WHERE
  [column] LIKE '%&gt;%'
Tested this in the Query Analyzer so not sure if it would work on web pages. To make sure nothing goes wrong (unlikely though), backup your data first.

No comments: