SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 add carriage and line break
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nbalraj
Starting Member

21 Posts

Posted - 08/09/2013 :  08:59:56  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 08/09/2013 :  09:04:13  Show Profile  Reply with Quote
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 - 08/09/2013 :  10:09:34  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 08/09/2013 :  11:05:07  Show Profile  Reply with Quote
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 - 08/12/2013 :  16:09:31  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
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

USA
808 Posts

Posted - 08/13/2013 :  14:26:14  Show Profile  Reply with Quote
Wouldn't you need the HTML break "<br>" to display in a new line?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000