replacing data in SQL server

Question

I have multiple tables with corrupted data like this:

Good data</title>and some random HTML<p></p><

What I would like to do is only keep "Good data" in the cell and remove everything else. I thought I can just create a small project with EF and clean the db with C# but there might be a quicker solution with SQL only? Can you use regex or some kind of substring function in SQL?

I will manually look at the table and select the field that needs to run through the code, there is no need to automate that at this point.

So should this data never contain any HTML? In other words, you want to strip all the data starting at the first `<`? — Aaron Bertrand, Aug 22 '13 at 21:04
Yes, it always starts with < and I want to remove everything that goes after that. — bobek, Aug 22 '13 at 21:08
Starting defensive measures... [Don't try to parse this data with Regex](http://stackoverflow.com/a/1732454/209259). — Erik Philips, Aug 22 '13 at 21:14
Defensive measure #2 -- make a backup copy of the data before trying to fix it — Gary Walker, Aug 22 '13 at 21:20

score 4 · Accepted Answer · answered Aug 22 '13 at 21:05

4

UPDATE dbo.SQLInjectionVictimTableName
  SET UnprotectedColumn = LEFT(UnprotectedColumn, CHARINDEX('<', UnprotectedColumn) - 1)
  WHERE UnprotectedColumn LIKE '%<%';

answered Aug 22 '13 at 21:05

Aaron Bertrand

272,866
37
466
490

score 0 · Answer 2 · answered Aug 22 '13 at 21:06

0

If good data is consistently followed by </, you could use:

UPDATE YourTable
SET BadField = LEFT(Badfield,CHARINDEX('</',BadField)-1)
WHERE CHARINDEX('</',Badfield) > 0

answered Aug 22 '13 at 21:06

Hart CO

34,064
6
48
63

replacing data in SQL server

2 Answers2