At first glance, you have an interesting query here in that the percent symbol (%) is actually the wildcard used by the LIKE expression in a SQL statement, so you have to escape it. Although, this may not be entirely necessary, allow me to explain by example:
What your LIKE expression is actually saying is "Return rows where dataNtextends with the phrase '20'." (The percent sign in the front is assumed to be the wildcard, and since you don't have a wildcard after the number 20, it is expected this will appear at the end of the value, not in the middle like you probably want... So, it is likely this query won't return any rows except by rare chance.)
But, even though you can fix this by throwing in an ESCAPE onto your LIKE, I don't think you need to ... In fact, you could remove the entire WHERE clause---which means all rows would be eligbile for updating---because if there isn't anything found to REPLACE, then nothing is damaged.
Final note: are you sure "%20" is what is causing you problems? This is just a URL-encoded space character, which you might find are somewhat common in some site links. I don't think i've ever run into any problems that have required me to manually tweak the database in order to publish that were caused by spaces in a URL. So... just be sure this really is the problem (and backup your database beforehand!)
Remove bad characters from fields in db
I've got an issue with a site where it will not republish the site because it is finding fields with bad characters in them.
This links to the exact problem.
http://our.umbraco.org/wiki/how-tos/fix-odd-publishing-problems
What sql script can I run to get rid of them? the bad characters are mostly %20
I'm using this but it doesn't seem to get rid of them.
Pete
At first glance, you have an interesting query here in that the percent symbol (%) is actually the wildcard used by the LIKE expression in a SQL statement, so you have to escape it. Although, this may not be entirely necessary, allow me to explain by example:
What your LIKE expression is actually saying is "Return rows where
dataNtextends with the phrase '20'." (The percent sign in the front is assumed to be the wildcard, and since you don't have a wildcard after the number 20, it is expected this will appear at the end of the value, not in the middle like you probably want... So, it is likely this query won't return any rows except by rare chance.)But, even though you can fix this by throwing in an
ESCAPEonto your LIKE, I don't think you need to ... In fact, you could remove the entire WHERE clause---which means all rows would be eligbile for updating---because if there isn't anything found to REPLACE, then nothing is damaged.Final note: are you sure "%20" is what is causing you problems? This is just a URL-encoded space character, which you might find are somewhat common in some site links. I don't think i've ever run into any problems that have required me to manually tweak the database in order to publish that were caused by spaces in a URL. So... just be sure this really is the problem (and backup your database beforehand!)
Good luck to you!
is working on a reply...