CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlunicodecharacter-encoding
Alan Ford
Alan Ford

Reputation: 375

Why is Turkish Lira symbol ₺ replaced with ? in SQL server 2008 database

Any idea why the Turkish Lira symbol is replaced by a question mark when I insert it in a table in the database. See the image below

enter image description here

Upvotes: 4

Views: 2790

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

This is not a font issue. This is a Unicode (UTF-16) vs 8-bit Code Page character set issue (i.e. NVARCHAR vs VARCHAR). The character you are trying to use does not exist in the particular Code Page indicated by the default Collation of the DB in which you are executing this query. The Code Page used by the DB's default Collation is relevant here since your string literal is not prefixed with an upper-case "N". If it was, then the string would be interpreted as being Unicode and no conversion would take place. But since you are passing in a non-Unicode string, it will be forced into the current DB's default Collation's Code Page as the query is parsed. Any characters not available in that Code Page, and not having a Best-fit mapping, get turned into "?".

You can run the following to see for yourself:

SELECT '₺';
PRINT '₺';

It both prints AND displays in the results grid as ?

If you want to see what character SQL Server thinks it is, run the following:

SELECT ASCII('₺');

And it will return: 63

If you want to see what character has an ASCII value of 63, run this:

SELECT CHAR(63);

And it will return: ?

Now run this:

SELECT N'₺';
PRINT N'₺';

This will both print and display in the results grid correctly.

To see what character value the symbol really is, run the following:

SELECT UNICODE(N'₺'), UNICODE('₺');

This will return: 8378 and 63

But isn't 63 the question mark? Yes. That is because not prefixing the string literal '₺' with a capital "N" tells SQL Server that it is VARCHAR and so it gets translated to the default unknown character.

Now, if you were to execute this VARCHAR version in a DB that had a Collation tied to a Code Page that had this character, then it would work even when not prefixing the string literal with an upper-case "N". However, at the moment, I cannot find any Code Page used within SQL Server that supports this character. So, it might be a Unicode-only character, at least at far as SQL Server is concerned.

The way to fix this is:

  1. Change the datatype of the field to NVARCHAR (I see in a comment on the question that the field is currently VARCHAR). If the field is VARCHAR then even if you use the N prefix on the string, the character will still get stored as ?, unless the Code Page specified by the Collation of the column supports this character, but again, I think this might be a Unicode-only character.
  2. Change your INSERT statement to prefix the string field with a capital "N": (73, 4, N'(3) ₺'). Even if you change the field to NVARCHAR, if you don't prefix the string with N then SQL Server will translate the character to ? first and then insert the ?. This is because the query gets parsed before it gets executed, and parsing (for non-Unicode string literals and variables) is done in the Code Page of the DB's default Collation

Upvotes: 5

Baldrickk
Baldrickk

Reputation: 4409

Probably for the same reason my browser isn't displaying it in the title for this question: It isn't in the application's character set (or maybe not supported by the font).

In this case, my browser shows some numbers in a box (denoting the character code). SQL-server is translating it to a known character instead.

Ensure you're storing it in a field that supports the character in it's character set (I think UTF-8 is sufficient)

Upvotes: 1

Related Questions