Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hidden carriage returns Expand / Collapse
Author
Message
Posted Thursday, October 29, 2009 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
My front end is a .Net web application. Users can enter text (which will subsequently be used as the 'body' text in an email) in a custom 'rich text box' in the browser. Something like the box I am typing in now - where you can format the text with html markup.

The custom box in question - let's call it HTMLBox1 - has these methods ... HTMLBox1.Text and HTMLBox.Plain

The first method gives you the html of what is entered in the box. The second method is supposed to give you plain text.

I save the html text and the plain text in separate text fields in a SQL Server 2008 database.

For example: The data entered in the rich text box might be ...

Dear Customer,
We'd like to invite you to a seminar etc.


In the database field that stores the HTML version of the text, this might get stored as

<p>Dear Customer,<br />We'd like to invite you to a seminar</p>

In the database field that stores the PLAIN version of the text, this is getting stored as:

Dear Customer, & n b s p; We'd like to invite you to a seminar

The custom Rich Text Box is outputting the html markup for a space (which I have put spaces in above so it shows) in what is supposed to be the PLAIN text output. Later a web service retrieves the data and sends emails using the data as the body of the email. (The .Net smtpClient sends two versions of the email, an HTML version and an alternate PLAIN version.) Most people who receive the email, who view it as Plain text, seem to see it okay. Most email clients seem to translate the html markup for a space into a carriage return.

But, some users - who are viewing their emails as Plain text - are complaining that they see the html markup.

So, my question is, when the 'Plain Text' version of whatever is entered in the Rich Text box is being written to the database, how can I replace the html markup for a space with a carriage return that, when the data is subsequently retrieved and sent as the body of an email, will contain a carriage return that all email clients are happy with?

As an aside, I note that when I store data entered into a <textarea> in the front end into a text field in SQL Server, if I look at the text (using the example above) the data in the SQL Server Text field looks like this:

Dear Customer, We'd like to invite you to a seminar

If I retrieve this data and display it in a web browser, it displays like this:

Dear Customer,
We'd like to invite you to a seminar


If you View Source in the browser there is a '<br />' after the comma in the first line - to give you your line feed.

But, what is being stored in SQL Server that is being returned to a front end, that means the front end says 'Ahh, I need to put a '<br />' here. If you look in the SQL Server text field there are no characters showing that would indicate a carriage return (or line feed) is being stored. I'm baffled. How does the front end know there is a carriage return (or line feed) embedded in what is being returned from the database.

Thanks for any help.
Post #810843
Posted Thursday, October 29, 2009 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
i'm pretty sure there is a CHAR(13) + CHAR(10) in there, that you are not seeing, but is part of the data...THAT is what is getting replaced witha < br /> tag, and not a space.

this is especially true in SSMS when looking at data in the grid: the CrLf is in the data, but is NOT used in the gridview, because you cannot scroll DOWN in a cell, so it is stripped out for presentation purposes in SSMS; switching to Text View(control+T) would prove this.

for example, i'm sure your data looks like this:
Dear Customer, & n b s p; [CrLf] We'd like to invite you to a seminar[CrLf]

and when converted to html changes or appends a < br /> tag to the end of the [CrLf]

if you use a different text editor, for example Editplus, you can use it to see spaces,tabs and CrLf a lot easier. most Notepad replacements like Editplus, UltraEdit, NotePad++ and many others do the same.

see how a space is a floating dot, a CrLf is the paragraph sign and a tab is double arrow thingy...makes it much easier to visualize using a better text editor like this.






Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #810947
Posted Thursday, October 29, 2009 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
Hi, and thank you for your reply.

I ran 'Select TextPlain FROM DistListEmailTbl' and looked at it in Text View. The text looked like this:

Dear Committee Member,
We'd like to invite you to etc.

But no sign of a carriage return or (char) 13 ... or anything else. Would you expect that these would still be invisible?

This proves there is some sort of a return in there, I'd like to know exactly what it is. Users are pasting data into the Rich Text box from Outlook where they use Word as a text editor. What gets pasted in there is truly awful. Loads of Office specific tags, references to style sheets that don't exist outside Office etc etc. The plain text generated by the Rich Text box's HTMLTextBox1.Plain method, is not accurate - so I want to write a routine to strip all the HTML tags out of the text - to create my own 'plain text' version. What should I embed as a carriage return that will go into the Sql Server field and, when retrieved, provide carriage returns in email clients that only interpret plain text and which, for example, are running on UNIX - not Windows.?

Thanks again.
Post #811092
Posted Thursday, October 29, 2009 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
I'm sure the CrLf is in there, it's just not visible based ont he tools you are using;
Proof: you said it looks like this:

Dear Committee Member,
We'd like to invite you to etc.

two lines means there is a CrLf there.

I've used this code to strip HTML out.
try it on one of your columns, view it in TextMode, and confirm the results span more than one line;
if it does not, you might want to repalce
tags with CHAR(13) +CHAR(10), which will make it plain text readable for both Win and Unix(which uses just CHAR(10) i believe for a new line)
--===== Replace all HTML tags with nothing
WHILE CHARINDEX(' CHARINDEX('<',@HTMLText)
SELECT @HTMLText = STUFF(@HTMLText,
CHARINDEX('<',@HTMLText),
CHARINDEX('>',@HTMLText)-CHARINDEX('<',@HTMLText)+1,
'')



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #811118
Posted Thursday, October 29, 2009 6:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
Again, thanks very much for your help. I'll give that a try.

I'm wondering if there a way to strip out all html tags but leave in ones that begin with <a

Plain text seems to handle hyperlinks okay and I'll need to leave them in there.

Cheers
Post #811307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse