How to escape (encode) new line symbols in SQL Server database tables?

[apostolchopov]

Hi, fellows!

Could you tell me how to escape (encode) new line symbols in SQL Server database tables? How to extract these data afterwards?

I have some tables with text and varchar(128) data fields and I would like to insert some strings that contain new line symbols. When I insert these data and then retrieve them back I get some strange symbols (like small squares) instead of new line symbols (#13 #10 in Delphi, 0x13 0x10 in C++).

Thank you in advance for your cooperation!

Regards!

 

[mrdenny]

This should give you a charage return and line feed.

char(13) + char(10)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[apostolchopov]

I have insert SQL statements in stored procedures. The text that I insert, and then subsequently retrieve, is passes as a parameter to the SQL stored procedure. I use a class wrapper of the OleVariant type in Delphi.

I see that your answer concerns program code written in C++. How to handle Delphi code handling this case?

Have you come across the situation described by me? Have you any idea why new line characters are not stored/retrieved properly in/from the database?

 

[mrdenny]

The code that I posted was actually T/SQL not C++. I've got no idea how to handle it in Delphi.

The SQL Server will store what ever data is passed to it from the client app.

Is the OleVariant data type in Delphi a uni-code data type? If so try using a non uni-code data type. If that does the trick that's because you are putting the data from a uni-code data type to a non-uni-code data type.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com