Replace() on a field with line breaks in it?

So I have a field that's basically storing an entire XML file per row, complete with line breaks, and I need to remove some text from close to three hundred rows. The replace() function doesn't find the offending text no matter what I do, and all I can find by searching is a bunchy of people trying to remove the line breaks themselves. I don't see any reason that replace() just wouldn't work, so I must just be formatting it wrong somehow. Help?

Edit: Here's an example of what I mean in broad terms:

<script>...</script><dependencies>...</dependencies><bunch of other stuff></bunch of other         stuff><labels><label description="Field2" languagecode="1033" /></labels><events><event           name="onchange" application="false" active="true"><script><![field2.DataValue = (some equation);

</script><dependencies /></event></events><a bunch more stuff></a bunch more stuff>

I need to just remove everything between the events tags. So my sql code is this:

replace(fieldname, '<events><event name="onchange" application="false" active="true"><script><![field2.DataValue = (some equation);

</script><dependen开发者_运维技巧cies /></event></events>', '')

I've tried it like that, and I've tried it all on one line, and I've tried using char(10) where the line breaks are supposed to be, and nothing.

---

Nathan's answer was close. Since this question is the first thing that came up from a search I wanted to add a solution for my problem.

select replace(field,CHAR(13)+CHAR(10),' ')

I replaced the line break with a space incase there was no break. It may be that you want to always replace it with nothing in which case '' should be used instead of ' '.

Hope this helps someone else and they don't have to click the second link in the results from the search engine.

---

Worked for me on SQL2012-

UPDATE YourTable SET YourCol = REPLACE(YourCol, CHAR(13) + CHAR(10), '')

---

If your column is an xml typed column, you can use the delete method on the column to remove the events nodes. See http://msdn.microsoft.com/en-us/library/ms190254(v=SQL.90).aspx for more info.

---

try two simple tests.

1. try the replace on an xml string that has no double quotes (or single quotes) but does have CRLFs. Does it work? If yes, you need to escape the quote marks.

2. try the replace on an xml string that has no CRLFs. Does it work? Great. If yes use two nested replace() one for the CRLFs only, then a second outter replace for the string in question.

---

A lot of people do not remember that line breaks are two characters (Char 10 \n, and Char 13 \r)

replace both, and you should be good.

SELECT 
REPLACE(field , CHR(10)+CHR(13), '' )
FROM Blah..