How to save unicode character in SQL database

I am trying to save ThinSpace (u2009) to the database using ADO.NET classes, but instead I am getting “?” symbol in db in place of ThinSpace. String that I save to db in UTF8 format.

        var content = "This is a test";

        byte[] bytes = Encoding.Default.GetBytes(content);
        content = Encoding.UTF8.GetString(bytes);

        content = content.Replace(' ', 'u2009');

        using (var con = new SqlConnection(connStr))
        {
            con.Open();

            using (var com = new SqlCommand("insert into Messages values ('" + content + "')", con))
            {
                com.ExecuteNonQuery();
            }
        }

​x
 
        var content = "This is a test";​        byte[] bytes = Encoding.Default.GetBytes(content);        content = Encoding.UTF8.GetString(bytes);​        content = content.Replace(' ', 'u2009');​        using (var con = new SqlConnection(connStr))        {            con.Open();​            using (var com = new SqlCommand("insert into Messages values ('" + content + "')", con))            {                com.ExecuteNonQuery();            }        }​

Result in DB

Answer

I believe you’re trying to insert unicode into a VARCHAR column. The SQL below:

CREATE TABLE MessageBodies
(
     NVARCHARText NVARCHAR(255),
     VARCHARText VARCHAR(255)
);

DECLARE @Text AS NVARCHAR(MAX) = NCHAR(2009);

INSERT INTO UnicodeInserts
VALUES (@Text, @Text);

SELECT
    *
FROM 
    MessageBodies;

 
CREATE TABLE MessageBodies(     NVARCHARText NVARCHAR(255),     VARCHARText VARCHAR(255));​DECLARE @Text AS NVARCHAR(MAX) = NCHAR(2009);​INSERT INTO UnicodeInsertsVALUES (@Text, @Text);​SELECT    *FROM     MessageBodies;​

Returns:

We see that SQL Server Management Studio renders a ? for an unknown character in a VARCHAR column. You’ll need to convert your column to an NVARCHAR for the text to render as expected.

Advertisement

Answer