The other day a creative Russian hacker caused me to do some database clean-up. I won’t tell the whole story, in a nutshell be sure to protect your sites from SQL Injection. I guess my next post will be some SQL injection prevention methods.

Anyways, I discovered that the usual replace method does not work on Text fields, just varchar and similar. And when I did a quick Google search, I found a lot of way too complicated approaches. So here is the simplest way.

First, here is the usual update replace method you might be familiar with:

SET StringtoEdit = REPLACE(StringtoEdit, ‘to-be-replaced’, ‘replace-with’)

(so in every entry of the column “Stringtoedit” any instances of the “to-be-replaced” are replaced with “replace-with”)

But if you try this method with a column that has the setting type of “Text”, you will get this rather vague error:

Argument data type text is invalid for argument 1 of replace function.

So here is the easy solution I found:

SET StringtoEdit = REPLACE(SUBSTRING(StringtoEdit , 1, DATALENGTH(StringtoEdit )), ‘to-be-replaced’, ‘replace-with’)

I have bolded the new elements of the SQL command. Try it out, its way easier then most solutions out there.

This entry was posted on Tuesday, November 24th, 2009 at 1:12 am and is filed under General WebDev. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.