CODEWITHSUNDEEP
htmlsqlsql-server-2008emailtext
JosephStyons
JosephStyons

Reputation: 58825

Carriage return that works in both HTML and in text

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

Answers (3)

Hussam
Hussam

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

Zeta Two
Zeta Two

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

user596075
user596075

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

Related Questions