Data in a table with carriage return?

Question

In SQL SERVER Is it possible to store data with carriage return in a table and then retrieve it back again with carriage return.

Eg:

insert into table values ('test1

test2

test3

test4');

when i retrieve it, I get the message in a line

test1 test2 test3 test4

The carriage return is treated as a single character.

Is there way to get the carriage returns or its just the way its going to be stored?

Thanks for the help guys!!!

Edit: I should have explained this before. I get the data from the web development (asp .net) and I just insert it into the table. I might not be doing any data manipulation.. just insert.

I return the data to the app development (C++) and may be some data or report viewer.

I don't want to manipulate on the data.

Answer 1

Can you please clarify how you retrieve the data back from the database? What tool do you use? The data probably contains the carriage returns but it's not displayed if you get the results in grid (try the results in text option)

Answer 2

You might need to put in a "\n" instead of a literal carriage return.

Answer 3

You can store Carriage return in the database. The problem here is that you are using SQL Server Management Studio to display the results of your query. You probably have it configured to show the results in a grid. Change the configuration of SSMS to show results to text and you will see the carriage returns.

Right click in the query window -> Results To -> Results To Text

Run your query again.

Answer 4

The carriage return is stored as is. The problem here is that your sql client is not understanding it. If you did a raw dump of this data you'll see that the carriage returns are there in the data.

I use DBArtisan at work and it seems to work fine. However isql seems to have the same problem that you reported.

Answer 5

IIRC, using chr(13) + chr(10) should works.

insert into table values ('test1' + chr(13) + chr(10) + 'test2' );

Answer 6

Is this result in your HTML or in Query analyser? If it's in HTML, have a look at the source code and it might appear correct there, in which case you'd have to replace the crlf characters with <br /> tags.

I'm also thinking that there used to be attributes you could add to an HTML textarea to force it to send carriage returns in certain ways -- soft or hard? I haven't looked that up, perhaps someone could do that.

But SQL Server does save the two characters in my experience. In fact I did exactly as you described here a few days ago using SQL 2005 and each line break has two unprintable characters.

Answer 7

Hello,

insert into table values 
('test1' + CHAR(10) + 'test2' + CHAR(10) + 'test3' + CHAR(10) + 'test4')

should do it. To see the effect, switch the query result window to plain text output.

Regards