Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 add carriage and line break

Author  Topic 

nbalraj
Starting Member

21 Posts

Posted - 2013-08-09 : 08:59:56
Hi All,

I've this query to include line break and carriage return.

substring (( select cast (Note as nvarchar(max)) + ' ' + Person + ', ' + convert(varchar(20),Notedate,100) + char(13) +char(10) FROM Note ORDER BY noteid DESC FOR XML PATH('')),1,2000) [Notes]

the output looks like:
this is a test joe, aug 09 2013 9:00AM this is a test2, joeblow, aug 09 2013 10:00AM this is a test3, joeblow, aug 09 2013 10:05AM

but i need the output like:
this is a test joe, aug 09 2013 9:00AM
this is a test2, joeblow, aug 09 2013 10:00AM
this is a test3, joeblow, aug 09 2013 10:05AM

Please let me know if i'm missing something

Thanks,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-09 : 09:04:13
That is just an artifact of the output being sent to grid. Change the output to text and you will see the CR+LF. To change the output destination, use the menu Query -> Results To -> Results To Text (or control-t). Control-d to change back to grid output.
Go to Top of Page

nbalraj
Starting Member

21 Posts

Posted - 2013-08-09 : 10:09:34
Hi,

Thanks for the reply. I'm using the query result output directly to show on the webpage.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-09 : 11:05:07
Look at the binary data (using a debugger?) to see if you are actually getting CR and LF to the page, or whether your rendering engine or somebody else along the way is stripping those out.

Alternatively, simply send an ascii string (not from SQL Server, from your test fixture) that has CR+LF to your page and see if that displays correctly. If it does, compare the strings coming from SQL Server vs your test string.

If the test string is not being displayed correctly as well, the problem is not at the SQL Server end, it has something to do with your client code.
Go to Top of Page

nbalraj
Starting Member

21 Posts

Posted - 2013-08-12 : 16:09:31
Hi,

Is there any other alternate option/way to display the required output other than usage char10 and char13?

Thanks for any inputs.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-08-13 : 14:26:14
Wouldn't you need the HTML break "<br>" to display in a new line?
Go to Top of Page
   

- Advertisement -