Update SQL Keep Characters to the Left

Today I ran across an issue with an SQL table that received an injection that inserted bad data at the end of certain fields in all of the records.  I had to write some code that would find the bad data and strip it off of the end of the good data.  After writing the code, I thought I would share with those interested.

Example:

In this specific example, the tblNews table received a SQL Injection that inserted a long strand of bad data at the end of the Body field.  Lucky for me, the Injections all started with '<div style='.  That specific hunk of characters is what I used to track them all down.  I first created a query to pull all the records to see what I was looking at.  Then typed an Update statement that removed all the characters to right of  '<div style='.

Initial Pull:

SELECT * from tblNews
where Body like '%<div style=%'

Update Code:

UPDATE tblNews
Set Body = LEFT (Body, CHARINDEX('<Div style=', Body) - 1)
where Body like '%<div style=%'

If you like this post and want to see more, follow me on my website www.chadcompton.com Or if you prefer...