Returning non-printable sybol (enter) to SQL Server from C# via CLR

I have the following CLR function:

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString PrintText(SqlString text)
    {
        // Put your code here
        return new SqlString(text.Value);
    }

And I want to get an enter symbol when passing

'\r\n'

to it.开发者_如何学C But insteat I get

'\r\n'

Could you please tell me what's wrong with this code.

Thank you.

---

In T-SQL you don't write a line break as \r\n. Instead you just use a line break:

'this
is a
string
in SQL
with
line breaks'

If you pass a string with \r\n to the C# code, nothing magical happens, it doesn't automatically get converted. The backslash character is just a character like any other. It's when you use the backslash in a literal string in the code that the compiler uses it as an escape code, and puts the control characters in the actual string.

---

You could always:

return new SqlString(text.Value.Replace("\\r\\n", "\r\n"));

---

From the SQL side, you could insert char(13) + char(10) into your T-SQL literals like so:

DECLARE @text varchar(100)

SET @test = 'this' + char(13) + char(10)
    + 'is a' + char(13) + char(10)
    + 'string' + char(13) + char(10)
    + 'in SQL' + char(13) + char(10)
    + 'with' + char(13) + char(10)
    + 'line breaks'

Though this works, it is much more verbose than Guffa's answer.

However, this technique can also be used to insert any character of the default code page into a string. The Function CHAR(int) accepts any integer between 0 and 255. Values outside that range cause it to return null. The function NCHAR(int) accepts values up to 65535 and inserts the corresponding unicode characters into a unicode string. Functions ASCII(char) and UNICODE(nchar) perform the inverse operations.