Saving Carriage Return in a nvarchar field (SQL Server 2005)

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

Hi George,

To resolve this issue, you need to ensure that insert/update operations are presenting character data which includes CR/LF's in a manner that is recognisable to mssql:

create table #crTest
(
charCol nvarchar(100) not null
);

declare @.s nvarchar(100);

set @.s = 'this is a test:' + nchar(13) + 'new line';
insert into #crTest values(@.s);

select * from #crTest;

In the above example, the CRLF is represented as the ASCII code 13 - sql is able to recognise and preserve formatting during subsequent interrogations.

To correct existing data, you could use the REPLACE function as such:

update MyCharTable

set problematicColumn = replace(problematicColumn, '?', nchar(13));

However this assumes you do not have a valid "?" somewhere in the "problematicColumn" column.

Cheers,

Rob

|||

Thanks for your reply Robert,

The problem is that I could have some valids "?", so I would have to make mssql understand that the chr(13) is indeed a chr(13) and not to convert it to "?"

According to your answer, if I'm storing the data from vb.net to mssql, is it possible to do this ?:

SqlStr as string = "Insert into table (field1nvarchar) Values ('" & Var1.replace(chr(13),nchar(13)) & "')"

or how should I do it ?

Thanks in advance.

George

|||

Hi George,

Sorry for the delay in responding - I've been off.

If you're using vb, they where you would normally insert your VBCRLF (or is it Environment.Newline in vb too now?) you would insert a char(13):

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

AdoCommand.Execute("Insert into MyTable (column1Nvarchar) values (" & sVal + ")")

The point to keep in mind is that REPLACE is a SQL function and if you're able to control how CR's are represted to sql at the time of insert/update, then you do not need it - simply use CHAR(13) to tell mssql to store a CR.

Cheers,

Rob

|||

I'm having this same problem too. However; there is no NCHAR(13) in VB.NET that I know of. It is in MSSQL though. Should I do something like use chr(13) instead of vbCrLf and then REPLACE all chr(13)'s with a NCHAR(13) on the SQL side in my stored procedure that saves my data? By doing this, will these be automatically converted back to vbCrLf's on the VB.NET side so that my data will show properly in my multiline textbox?

Thanks

|||

Hi George,

Yes, you'll have to replace the vbCrLf with NCHAR(13) in the query/parameter string. It's not obvious, but if you look closely in my previous post, the NCHAR(13) is now encolsed in a string:

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

So in your proc, I assume you've a nchar/char parameter that accepts the string param from VB - in this case, sVal. If the @. param of the sp is a nchar, then in the above scenario, the actual CRLF will be included in the @. param - you won't need to do anything further to recognise and store the CR/LF on the mssql side.

Cheers,
Rob