Skip to main content
50 votes
Accepted

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). ...
Solomon Rutzky's user avatar
44 votes
Accepted

How to create Unicode parameter and variable names

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃. The problem you are having here is due entirely to the ...
Solomon Rutzky's user avatar
37 votes
Accepted

Querying non-ASCII rows from Postgres

To answer your question: [:ascii:] works. You may have some characters in your text that you do not recognize as non-ASCII, yet they're there. They can be something like a non-breakable space, for ...
joanolo's user avatar
  • 13.5k
31 votes
Accepted

Why does a comparison between 'tr' & 'tR' fail on a SQL Server with Vietnamese_CI_AI collation?

Given that this behavior is present in the newer version of that collation, and that combinations such as "fr" and "fR" do match (as expected), it could only be culture-specific ...
Solomon Rutzky's user avatar
30 votes
Accepted

Can't update "CO2" to "CO₂" in table row

The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant: UPDATE test SET description = N'CO₂' WHERE id = 1;
gbn's user avatar
  • 69.9k
22 votes

Can't update "CO2" to "CO₂" in table row

@gbn already explained the basic reason and fix, but the specific reason for the behavior that you are seeing is this: You are using a VARCHAR literal (no N prefix) instead of an NVARCHAR literal (...
Solomon Rutzky's user avatar
22 votes

How to create Unicode parameter and variable names

I don't think it's Unicode that's causing the problem; in the case of local variable or parameter names, it's that the character isn't a valid ASCII/Unicode 3.2 character (and there isn't any escaping ...
Aaron Bertrand's user avatar
17 votes

Accent Sensitive Sort

The behavior you are seeing here is due, in a general sense, to the fact that the Unicode Collation Algorithm (UCA) allows for complex, multi-level sorting. More specifically: Sorting is not ...
Solomon Rutzky's user avatar
14 votes
Accepted

Why does the varchar datatype allow unicode values?

But the Trademark(™) and Registered(®) symbols are Unicode characters. You are wrong here. Your strings contain only ascii characters. Here is a simple test that shows you that your characters are ...
sepupic's user avatar
  • 11k
13 votes

Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?

How one "character" (which can be comprised of multiple Code Points: surrogate pairs, combining characters, etc) compares to another is based on a rather complex set of rules. It is so complex due to ...
Solomon Rutzky's user avatar
13 votes
Accepted

Accent Sensitive Sort

This question is not so related to databases but more on Unicode handling and rules. Based on https://learn.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql ...
Patrick Mevzek's user avatar
13 votes

When `nvarchar/nchar` is going to be used with SQL Server 2019?

UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on ...
Paul White's user avatar
  • 86.4k
12 votes
Accepted

Fix corrupted utf8 nvarchar value that was inserted without N prefix

It is not possible to recover the original value. Character constants without the N prefix to denote national characters are translated according to the database default collation code page. In cases ...
Dan Guzman's user avatar
  • 28.3k
11 votes
Accepted

Detect if any values in NVARCHAR columns are actually unicode

Suppose one of your columns does not contain any unicode data. To verify that you would need to read the column value for every row. Unless you have an index on the column, with a rowstore table you ...
Joe Obbish's user avatar
  • 32.5k
11 votes
Accepted

Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?

The problem is that you encoded a UTF-8 encoded string into Base64. So, decoding the Base64 gives you back the original UTF-8 sequence of bytes. SQL Server uses UTF-16 Little Endian only for NVARCHAR ...
Solomon Rutzky's user avatar
11 votes
Accepted

PostgreSQL nondeterministic collations are not supported for LIKE

Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation ...
Solomon Rutzky's user avatar
11 votes
Accepted

What is difference between Arabic_100_CS_AS_KS_WS_SC_UTF8 and Latin1_General_100_CS_AS_KS_WS_SC_UTF8?

Yes, all _UTF8 collations use code page 65001 as that is the code page for UTF-8. You can even use 65001 in a DOS / Command Window via: chcp 65001 though not all programs and fonts will work ...
Solomon Rutzky's user avatar
10 votes

What is the meaning of "-x-icu" in PostgreSQL's "collates"?

The documentation, 23.2. Collation Support / 23.2.2.2.2. ICU Collations , states: Collations provided by ICU are created in the SQL environment with names in BCP 47 language tag format, with a “...
Solomon Rutzky's user avatar
9 votes
Accepted

Storing unicode text into varchar column in SQL Server

The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value. Sorry to say ...
Dan Guzman's user avatar
  • 28.3k
8 votes
Accepted

Nvarchar to varchar to nvarchar characters conversion

No, there is no way to "fix" the data because the data is no longer there. When you converted to VARCHAR, the underlying values for each character were changed into the ASCII value for ?. ...
Solomon Rutzky's user avatar
8 votes
Accepted

ERROR: "sql " is not a known variable

Your error message says: psql:cobertura.sql:29: ERROR: "sql " is not a known variable LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... Look closely: "sql ", not "sql" That ...
Erwin Brandstetter's user avatar
8 votes
Accepted

SQL Server Bulk Insert properly interprets some Unicode characters but not others?

This is not a bug in SQL Server (or even in Windows), nor is it a situation that requires the additional step of converting the file into another encoding (i.e. into "Unicode", which in Windows-world ...
Solomon Rutzky's user avatar
7 votes

Why does the varchar datatype allow unicode values?

From the comments, I agree "Extended ASCII" is really bad term that actually means a code page that maps characters/code points in the 128-255 range, beyond the standard 0-127 code point range defined ...
Dan Guzman's user avatar
  • 28.3k
7 votes
Accepted

When `nvarchar/nchar` is going to be used with SQL Server 2019?

this can reduce the size of tables and indexes (emphasis added) Reduction in size is only possible if most of the characters are essentially [space], 0 - 9, A - Z, a - z, and some basic punctuation. ...
Solomon Rutzky's user avatar
6 votes
Accepted

How to be sure that a SQL Script is running with ANSI encoding?

So, how can I be sure, that when the user press F5, all script is encoded with ANSI, the same way we sent it, without these strange characters? You can't be sure. This is just the unfortunately ...
Solomon Rutzky's user avatar
6 votes

What is the SQL Server equivalent of MySQL's utf8mb4 character set?

First: the particular "edition" of SQL Server (i.e. Express, Standard, Enterprise, etc) does not matter. All editions for a particular version will behave the same.   When I switch to "...
Solomon Rutzky's user avatar
6 votes
Accepted

Special letter behaviour problem?

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string ...
Aaron Bertrand's user avatar
6 votes

Why does the varchar datatype allow unicode values?

But the definition of varchar says, it allows non-unicode string data. But the Trademark(™) and Registered(®) symbols are Unicode characters. Does the definition contradicts the property of varchar ...
Solomon Rutzky's user avatar
6 votes

Why isn't Unicode character REPLACEd in some cases?

This behavior is simply due to the fact that you are executing the query in a Database that has a pre-"100" series default Collation, in which case a great many characters do not have any sort weight. ...
Solomon Rutzky's user avatar
6 votes

Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database?

Arabic (as well as Hebrew and Syriac) are right-to-left languages. Hence they display in the opposite direction that the bytes are physical stored in. Having the proper display is controlled through ...
Solomon Rutzky's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible