How to store carriage return in SQL
There is an address list that is kept in Excel 2003 with 3 Columns ( Name, CompName, CompAddress). I am building a web-app that will allow users to edit the maililng list. First step was to bring the data into MSSQL 2008. No biggie. Except, in the CompAddress field, the originator had used carriage returns in order to format so she could do a mail merge in word and the address would be formatted correctly.
On SQL server in the ComAddress field after the import I see a "square" (assuming it always shows the for special characters). My problem is, how do I store that special character? I was hoping to just have a method that is an insert/update, just don't know how to store that same special character.
In excel she said she uses the Alt-Enter to get carriage return inside a cell.
I am open to Suggestions on solving differently.
On SQL server in the ComAddress field after the import I see a "square" (assuming it always shows the for special characters). My problem is, how do I store that special character? I was hoping to just have a method that is an insert/update, just don't know how to store that same special character.
In excel she said she uses the Alt-Enter to get carriage return inside a cell.
I am open to Suggestions on solving differently.
If you need to use CR or new line in a query you can use Char(10) and Char(13) respectively.
ASKER
Ok. well I assume you mean if a user uses "Shift-Enter" in a Text Box. Ok I can see that. Only hope MS Word when importing from an ODBC source can interpret that. Unfortunately though, when I pull the Data and set the value of Text Box with one of the old Excel created special characters, it is not showing a carriage return.
it's not "SHOWING" or selecting with it. You shouldn't SEE anything different in SQL.
ASKER
Except as I stated. The data that was imported into SQL from Excel has a "box character" that you can see.
Excel uses ANSI character 10 for in-cell line breaks, while usually a line break is ANSI 10 and 13.
Try replacing CHAR(10) with CHAR(10) + CHAR(13).
Try replacing CHAR(10) with CHAR(10) + CHAR(13).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
'123 Main Street
Anytown, USA 12345'
And then select it you will get back
123 Main Street
Anytown, USA 12345