Replacing line breaks in SQL server stored procedures

[mrMister]

I have a varchar(4000) in an SQL stored procedure which contains plain ASCII text. When it is displayed on a web page, it needs to be HTML formatted

so text like the following...

I am the company director of Green Widgets Inc.

I spend a lot of my time researching widgets and green things.

should become...

<p>I am the company director of Green Widgets Inc.</p>
<p>I spend a lot of my time researching widgets and green things.</p>

Has anyone got any ideas on how to replace all the newlines with "</p><p>"? The new lines could be in any format (UNIX, Mac or Windows)

Because of the way the application is structured, it would be better to have this logic in the stored procedure rather than in the ASP page.

[dataguy]

I have a similar situation, but when I display the field information in HTML I simply replace the line feed with a "<br>" and a linefeed.

It looks like this:
Response.Write(Replace(rsInfo("Article Body"), vbCrLf, "<br>" & vbCrLf)

Hope this helps....

[chrisjoha]

I think you should implement this in your application logic, not in an sql stored procedure. The database server is supposed to serve up the data, and the application should handle formatting. So, you can do something along the lines of Dataguys suggestion. Here's my suggestion using PHP:

$text = $db_row['text']; 
$text = '<p>' . str_replace(array("\n\n", "\n"), array('</p><p>', '<br />'), $text) . '</p>'; 

Then, depending on the integrity of your data, you might want to trim out empty <p>'s from that string. Good luck!

An even better way than the above is to retrieve the data one place and perform any needed algorithms on it, pass the parsed data to a GUI component in your application and THEN do the formatting. MVC! ;)