Parse <br> to plain text new paragraph

Question

I am currently in the process of removing html tags from fields within an internal database. Everything has gone smoothly except for turning
tags to plain text new line characters.

I would like to convert this:

The victory halted Spain&rsquo;s 35-game unbeaten streak, handing the Spanish their first defeat since November 2006. The Americans now advance to the final Sunday to face the winner of Thursday&rsquo;s semifinal between South Africa and Brazil, the five-time World Cup winner. Brazil defeated the Americans, 3-0, in their earlier meeting in this tournament.<br>
<br>
In the final, though, the United States will be without midfielder Michael Bradley, who received a red card for a harsh tackle in the 87th minute, the third such ejection for the Americans in this tournament. It was the only careless blemish on an otherwise nearly perfect evening.

in to this:

The victory halted Spain’s 35-game unbeaten streak, handing the Spanish their first defeat since November 2006. The Americans now advance to the final Sunday to face the winner of Thursday’s semifinal between South Africa and Brazil, the five-time World Cup winner. Brazil defeated the Americans, 3-0, in their earlier meeting in this tournament.

In the final, though, the United States will be without midfielder Michael Bradley, who received a red card for a harsh tackle in the 87th minute, the third such ejection for the Americans in this tournament. It was the only careless blemish on an otherwise nearly perfect evening.

I am using the following line of code to change the
to a new line character:

value = value.Replace("<br>", Environment.NewLine).Trim();

After running that code this is what is saved in my database:

The victory halted Spain's 35-game unbeaten streak, handing the Spanish their first defeat since November 2006. The Americans now advance to the final Sunday to face the winner of Thursday's semifinal between South Africa and Brazil, the five-time World Cup winner. Brazil defeated the Americans, 3-0, in their earlier meeting in this tournament.    In the final, though, the United States will be without midfielder Michael Bradley, who received a red card for a harsh tackle in the 87th minute, the third such ejection for the Americans in this tournament. It was the only careless blemish on an otherwise nearly perfect evening.

If I take the parsed text saved to my database and paste it into notepad or Word I get only one paragraph instead of two.

Is this the correct way to handle this? The database I am using is SQL Server 2005.

Answer 1

Have you tried replacing with actual newline characters? i.e.

value = value.Replace("<br>", "\r\n").Trim();

Granted Environment.NewLine should do this same thing but it's worth a shot.

Answer 2

Based on your follow up comment (when you debugged it), it sounds like the correct value is at least being sent to the database correctly.

It's probably not this simple, but worth checking... When you say you "ran a query to pull the value ... and pasted it to Word", what are you using to do the query? Because I know if you query something using SQL Server 2005 Management Studio in the default "Results to Grid" view, it doesn't render new lines properly (I think it just replaces them with spaces)... If you switch it to "Results to Text" (or you get the value from the database in your code & debug the value returned), you'll get a more accurate representation of the actual value, complete with new lines showing...

Answer 3

Your method of using Environment.Newline is correct. I believe the issue is with how some queries are returned directly in SQL Server, assuming you're copy/pasting directly out of SQL Server Management Studio (or similar).

I'm about 99% positive that if you pull the data out with a SqlConnection and then output it to a winform, text file, etc... then you'll get the line breaks you're looking for.

Sorry, but I can't recall why this happens when you copy/paste directly out of the grid of results in SQL Server.

Answer 4

I am curious how you are retrieving the "saved" value. Are you copying it from, say, SQL Server Management Studio, or actually performing a SELECT statement? Sometimes, the data grids that display information in the SQL Server 2005 tools don't display string data "exactly" as it is stored in the database. If you have not actually performed a SELECT statement, I would try that, and make sure you are not encountering a UI quirk.