How to concat SQL Server 2005 data with line feed
I am attempting to update a column in SQL Server 2005 by concatenating data from two other columns and include a line feed between the two and have the second line begin with a /.
The data will be viewed through an MS Access 2003 database with a linked table using an ODBC connection. The script I am using in MS SQL Server Management Studio Query window is:
update tblinventory set abstract = [title_1]+char(10)+char(13)+'/'+[Title_2] where ITEM_NO = 10850
However, when the results are viewed using Access I see in the abstract column data from title_one “two boxes” a / and data from title_two.
I would expect to see the /and data from title_two on a different line.
An example is data in title_1 = Houston data in title_2 = Texas; I would expect to see data returned in abstract as Houston new line /Texas However when viewed through the linked table in Access I see in column abstract Houston BOX BOX [two][/two]/Texas all on the same line the two boxes I assume are the special characters for line feed and carrige return but Access is not interpreting them correctly. When the same row of data is viewed through MS SQL Server Management Studio Query window the data for abstract is displayed on one line as: Houston /Texas.
Can anyone assist with this issue? Thanks in advance
Software/Hardware used:
SQL Server 2005, Access 2003
The data will be viewed through an MS Access 2003 database with a linked table using an ODBC connection. The script I am using in MS SQL Server Management Studio Query window is:
update tblinventory set abstract = [title_1]+char(10)+char(13)+'/'+[Title_2] where ITEM_NO = 10850
However, when the results are viewed using Access I see in the abstract column data from title_one “two boxes” a / and data from title_two.
I would expect to see the /and data from title_two on a different line.
An example is data in title_1 = Houston data in title_2 = Texas; I would expect to see data returned in abstract as Houston new line /Texas However when viewed through the linked table in Access I see in column abstract Houston BOX BOX [two][/two]/Texas all on the same line the two boxes I assume are the special characters for line feed and carrige return but Access is not interpreting them correctly. When the same row of data is viewed through MS SQL Server Management Studio Query window the data for abstract is displayed on one line as: Houston /Texas.
Can anyone assist with this issue? Thanks in advance
Software/Hardware used:
SQL Server 2005, Access 2003
ASKED:
February 1, 2011 10:46 PM
UPDATED:
February 2, 2011 6:25 PM
RELATED QUESTIONS
Answer Wiki:
Try:
<pre> + char(13) + char(10) +</pre>
If I remember well chr(13) = CR, and chr(10) = LF, and you should add CRLF (not LFCR).
-CarlosDL
--------------------
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you that seemed to fix the issue. Should have thought of the order myself.