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

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 2

INSERT INTO table values('test1' + CHAR(10) + 'test2' + CHAR(10) + 'test3' + CHAR(10) + 'test4')

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

Regards

Answer 3

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

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

Answer 4

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)