Storing crlf characters in a sql field

[sodakotahusker]

I have need to store some textual data with formatting so when the field is displayed in the application - the correct formatting is displayed. This is a very simple case of formatting. I just need to retain the newline sequences and the tabs. However it appears that the OD OA sequence seem to be getting stored as 20 20 (spaces).

Is there some limitation with these characters ? Do I need to have a different data type?

Thanks in advance!!!

 

[vongrunt]

create table blah ( someText varchar(255) )
insert into blah values ('One line
Another line')

select someText, charindex( char(13) + char(10), someText) from blah

drop table blah

Works for me... perhaps conversion happens outside database?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[sodakotahusker]

I think you are right on that. Data is being passed to a stored procedure through ADO. NET. I built the text string using the Environment.NewLine() function.

 

[sodakotahusker]

I have been monkeying with this all day long (off and on). I finally just tried your little example. From what I see that does not prove anything. I have a CRLF combo at the end of the field when I send it in from C#/AdoNET - and I only have it at the end of the field when I do something like this

update table set textfield = 'Hello' + char(13) + (char(10) + ' World'

When I select my CRLF combo contains spaces and there is a CRLF at the end of the text. Thus your charindex search seems to be destined to always bring back the position of the last two characters.

I put the profiler on this and captured the input. It shows the exact formatting that I hope to see on the output when the data is read. However when it stores in SQL, it will not save the CRLF characters as such.

I found one article that said preface the text with <pre> and follow it with </pre>.

Someone please rescue me from this quagmire. This project is overdue and I have two more due in a week.

My guess is that what I need is binary data for these characters to retain their special values. And the real bad news is that the output is read by a 3rd party application so I have no way to rig up a kludge fix like replace the characters with something wierd like @^ and replace them with CRLF when coming back.

 

[travisbrown]

Are you writing the selects for the third party app? If so, don't preface it with <pre></pre> when storing; do that on the output if need be.
Varchar and char fields will store chr characters just fine.

I'm guessing that the third party app parses html? If you are trying to display this in an html page, do something like this in the Select
REPLACE(REPLACE(yourcolumn,Chr(13),"<br />"),Chr(10),"<br/>")
or
'<pre>' + yourcolumn + '</pre>'

Note this isn't really the best way to deal with Chr(10), which is a linefeed rather than carriage return. Line feed should just drop the cursor and start the new line where the previous line leaves off.

I don't know if <pre> handles Chr(10) properly, but it's not any worse than the replace method. <pre> basically displays text as you see it in the html source.

 

[sodakotahusker]

Actually I have not even moved my changes to the environment where the 3rd party app is running. I was simply pasting the results of my select statement into UltraEdit and looking at it in hex mode. I see no evidence that control characters are not problematic with either varchar ot text fields. The field in question is nText but I changed it to varchar in my test database and that did not help. I also stored a varbinary - but when I converted that back to varchar - the same phenomena occurred - the crlf got changed to spaces.
I don't have the opportunity to do the selects for this 3rd party app. And I don't know if it handles HTML or not. It if handles html I could convert the crlfs to <br>'s.

 

[sodakotahusker]

I think I have been beating my head against a wall all day for nothing. I just thought I'd try to retrieve the data back into the C# program from SQL and see what comes out. It looks just fine. It seems to me that Query Analyzer is the component having the problem with the control characters. If I had not wanted to test this so thoroughly, I probably would have saved about 5 hours of frustration. Sometimes things are not WYSIWYG!
So I think I'm ok here. If not, I'll be back. Thanks for everybody's input.

 

[travisbrown]

If it doesn't handle html, then forget about <pre>

I haven't had a problem storing CHR() in text columns in SQL2000, inserting by a variety of methods. I regularly store text in text and char columns and use REPLACE(str,CHR(13),"<br />") to output for html.

When you paste in Ultraedit, are the linebreaks preserved even if you can't see the decimals, or is it all one long unbroken string?

How about saving results to file in query analyzer?

Have you tested to see if the third party app will render properly as is?

 

[travisbrown]

ah, you corked me on the posting.

cheers.