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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Update tableName
Set myField = N'text to be updated'
WHERE ID=23
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.