Reputation: 58825
I am working on some code that dumps data into a table.
Some of the data includes carriage returns.
Eventually, the data will be exported as an HTML-formatted email or in a text file (CSV), or both. I don't know in advance which export method will be used on this data.
If I write my carriage returns to the database like this:
UPDATE SOME_TABLE
SET TEXT_VALUE = @LineOne + CHAR(13) + CHAR(10) + @LineTwo
WHERE ID = @IDValue
Then it works in the CSV file just fine, but the HTML email gets totally screwed up - it confuses the encoding and displays the email with header and mime information and is totally useless except for debugging.
On the other hand, if I write my carriage returns to the database like this:
UPDATE SOME_TABLE
SET TEXT_VALUE = @LineOne + '<br>' + @LineTwo
WHERE ID = @IDValue
Then it's the other way around - it works in email but the text file outputs with the actual "br" embedded in the text, as in "LineOne< br>LineTwo".
Is there a way to represent a carriage return in a way that will produce the same result in both plain text and in HTML?
All of this is running on SQL Server 2008 from inside a T-SQL stored procedure.
Upvotes: 1
Views: 2772
Reputation: 510
I'm not sure what language you are using to send the email. If it's PHP, you can use your normal text with carriage returns and transform them to html-linebreaks using:
nl2br($text);
Upvotes: 0
Reputation: 1816
I think the most appropriate way is to store all text "as-is" with CR LF and then make the proper manipulations when you fetch the data from the table, ie. convert newlines to
if you are rendering html, etc.
Upvotes: 4
Reputation:
This is one of those times that there is no easy answer to this. HTML and plain text are interpreted very differently, as you are seeing here. A newline is different in both, and not compatibile in the other.
What you need to do is to separate the logic into different stored procedures: one for HTML/email formatting, and one for plain text/csv formatting.
Upvotes: 3