SQL Server 2005: How to keep new line symbols and others in SQL statements?

Hi experts!

I need to update records in several SQL Servers 2005 and I need advice. My plan is to use Update myTable SET myFiled=’new value’ WHERE ID=23
Problem is that field myField is nvarchar(4000) fields which consist new line symbols, tab symbols and so on, which symbols are lost via plan text. What options I have to resolve this?
dvplayltdAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This would do:

Update myTable
SET myFiled=N'new value'
WHERE ID=23

>> Problem is that field myField is nvarchar(4000) fields which consist new line symbols, tab symbols and so on, which symbols are lost via plan text.
If you want to have your new value to have
* New line then insert Char(13)
print 'newvalue' + char(13) + 'test'

* Tab then insert Char(10)

print 'newvalue' + char(9) + 'test'
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Update tableName
Set myField = N'text to be updated'
WHERE ID=23
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
dvplayltdAuthor Commented:
To rjegan17

I try with
UPDATE LEMailTemplates SET Msg=  'License' + char(13) + char(10) + 'agreemnt' + char(13) + 'here!' WHERE LEmailTemplateID =8

doesn't produce error, but result is not as expect. Next I read this value with NETFramework 2 and show it in text box where I do not see this char(13) like new line.

Text box is OK with showing multiline text.
0
 
dvplayltdAuthor Commented:
when I debug my NET application I review string which is write in SQL Server.

"L\r\nicense\ra\r\ngreemnt\rhere!"

for new line is used \r  - I try to put this \r in DB but it not work.
0
 
gladfellowCommented:
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Next I read this value with NETFramework 2 and show it in text box where I do not see this char(13) like new line.
>> for new line is used \r

If you want to display new line character in SQL Server, then it is \n or Char(13) and I'm not sure about .Net..
Kindly let me know where exactly you are trying to achieve....
0
 
dvplayltdAuthor Commented:
10x. You are right for this symbols, however it doesn't work. Finally I make 1 hour Copy / Paste via this Windows Application based on NET Framework which I talk about. Look that is has some own understand what is symbol for new line :-(.

Bad is that for future I will have others some sutiations ... however I do not have more time now for this. Thanks.
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
@dvplayltd: Can we have explanation why rrjegan17's answer is accepted? as neither of the solution worked for you. And if you're looking at the first line of the solution from his answer-

Update myTable
SET myFiled=N'new value'
WHERE ID=23

I have also suggested the same thing in my answer.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
rikin_shah,

Apart from the common code posted, I gave additional inputs as given below:

If you want to have your new value to have
* New line then insert Char(13)
print 'newvalue' + char(13) + 'test'

* Tab then insert Char(10)

print 'newvalue' + char(9) + 'test'

I hope this is where I provided additional explanation compared to your comment.
Hope you agree with me on this.
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Agreed, you have suggested more solutions. However dvplayltd has mentioned that they do not work for them.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
rikin_shah,

Agreed, based on the suggestions in comments http:#a35312435 and http:#a35312941, new line characters varies in SQL Server and .Net (which I am aware of) and hence dvplayltd did it manually for the entire data available with him now.

Anyhow if dvplayltd doesn't have any concerns, I don't have any issues in splitting points with you..
0
 
dvplayltdAuthor Commented:
To rikin_shah

You offer is veru far from what I ask for. I know how to update fields in DB, the question is about HOW TO insert new line!

While rrjegan17 is much more closer, in fact his answer is right, just my application do some strange .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.