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

Question

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><dependencies /></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.

you'll actually have to show us an example of the data and what you're passing to replace. — Fosco, Jun 06 '11 at 19:31
Probably a duplicate of: [Replace a newline in TSQL](http://stackoverflow.com/questions/951518/replace-a-newline-in-tsql) — Luka Milani, Jun 06 '11 at 19:45
@Luka No, I'm not trying to delete the line breaks themselves, I'm trying to remove text from a field that does contain line breaks. — extarbags, Jun 06 '11 at 19:52

score 1 · Answer 1 · answered Mar 30 '13 at 03:58

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.

score 1 · Answer 2 · answered Mar 11 '16 at 10:57

1

Worked for me on SQL2012-

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

answered Mar 11 '16 at 10:57

Gurpreet Singh

1,641
3
17
29

This is the real answer, among a bunch of SELECTs that replace nothing. – Henrik Erlandsson Nov 22 '19 at 12:26

score 0 · Answer 3 · edited Dec 05 '12 at 17:07

0

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..

edited Dec 05 '12 at 17:07

user1879829

3
2

answered Jun 06 '11 at 19:37

Nathan Tregillus

6,006
3
52
91

try the other way, CHR(13)+CHR(10) ? that should definately remove the newlines via tsql – Nathan Tregillus Jun 06 '11 at 20:00

score 0 · Answer 4 · edited Jun 07 '11 at 10:05

try two simple tests.

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.
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.

score 0 · Answer 5 · answered Jun 06 '11 at 20:10

0

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.

answered Jun 06 '11 at 20:10

Duncan Howe

2,965
19
18

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

5 Answers5