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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
when I debug my NET application I review string which is write in SQL Server.
"L\r\nicense\ra\r\ngreemnt
for new line is used \r - I try to put this \r in DB but it not work.
"L\r\nicense\ra\r\ngreemnt
for new line is used \r - I try to put this \r in DB but it not work.
Check out the following two articles that talk about introducing newline inside your data in SQL.
http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/
http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
Hope these two articles help you with your need.
- Venkat
http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/
http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
Hope these two articles help you with your need.
- Venkat
>> 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....
>> 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....
ASKER
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.
Bad is that for future I will have others some sutiations ... however I do not have more time now for this. Thanks.
@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.
Update myTable
SET myFiled=N'new value'
WHERE ID=23
I have also suggested the same thing in my answer.
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.
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.
Agreed, you have suggested more solutions. However dvplayltd has mentioned that they do not work for them.
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..
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..
ASKER
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 .
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 .
Set myField = N'text to be updated'
WHERE ID=23