How to save unicode character in SQL database

Question

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();
            }
        }

Result in DB

just add big n(N) before the variable like so `"insert into Messages values (N'myValue')"` — styx, Jan 13 '19 at 14:52
Don't build SQL queries through string concatenation. Use parameterized queries. If you're not familiar with what SQL injection is, become familiar with it. — Daniel Mann, Jan 13 '19 at 15:12
@Daniel Mann I know what is SQL injection. This code written just like example for Stackoverflow. This is not production code. :) — Polaris, Jan 13 '19 at 18:30
Possible duplicate of [Ukrainian character change to question mark when insert to table](https://stackoverflow.com/questions/46196062/ukrainian-character-change-to-question-mark-when-insert-to-table) — Ilyes, Jan 13 '19 at 19:28

score 4 · Accepted Answer · edited Jan 13 '19 at 15:47

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;

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.

score 2 · Answer 2 · answered Jan 13 '19 at 15:09

may be this might help:

using (var com = new SqlCommand("insert into Messages values (@content)", con))
{
    var param = new SqlParameter("@content", SqlDbType.NVarChar);
    param.Value = content;
    com.Parameters.Add(param);
    com.ExecuteNonQuery();
}

your target column data type is needed to be NCHAR or NVARCHAR

How to save unicode character in SQL database

2 Answers2