Hot answers tagged

45

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). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character. Windows and SQL Server ...


44

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 classification of the character(s) being used. The rules for regular (i.e. non-delimited) identifiers are: First letter must be: A letter as defined by the Unicode ...


30

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 instance, or any other Unicode space character. It is not strange to have non-breakable spaces ( ) in texts that you copy-and-paste from a web page, yet ...


30

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;


29

All character data in SQL Server is associated with a collation, which determines the domain of characters that can be stored as well as the rules used to compare and sort data. Collation applies to both Unicode and Non-Unicode data. SQL Server includes 3 broad categories of collations: binary, legacy, and Windows. Collations in the binary category (_BIN ...


26

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


26

The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like: vowels (lines and dots that are typically under the letters): base letter "ה" = "h"; "הֶ" =...


22

@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 (string with N prefix), hence the Unicode character will get converted into VARCHAR. VARCHAR is an 8-bit encoding that is, in most cases, one byte per character, ...


22

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 sequence for variables/parameters like there are for other entity types). This batch works fine, it uses a Unicode character that simply doesn't violate the ...


16

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 Comparison: Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But ...


15

This has something to do with the COLLATION of your database (more information in BOL). I'm not entirely sure of the language of the specific character you're having a problem with (I'm guessing Persian based on this thread), but if you specify the correct collation in the equality operator, then you get accurate results. if nchar(65217) COLLATE ...


15

But the Trademark(™) and Registered(®) symbols are Unicode characters. Your are wrong here. Your strings contain only ascii characters. Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255): declare @VarcharUnicodeCheck table ( col1 varchar(100) ) insert into @...


13

You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below). Technically, you can store Japanese characters in VARCHAR fields if you use a Japanese_* Collation that is associated ...


13

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 needing to account for all the various (and sometimes "wacky") rules found in all of the languages represented in the Unicode specification. This system applies ...


13

This question is not so related to databases but more on Unicode handling and rules. Based on https://docs.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql Latin1_General_100_CS_AS means: "Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252" with the added CS = Case Sensitive and AS = Accent ...


13

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 the basis of actual requirements for comparison, sorting, data import, and export. You may need to change more than you think, since e.g. an nchar(1) type ...


11

This is a well known "quirk" of SQLite. SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite. Take a look here: SQLite uses a more general dynamic type system. In ...


11

Without having an example of the data as well as the table DDL from the O.P., it is difficult to say for certain what the exact cause of this error is for the O.P. However, this behavior (and hence problem) can happen for others for the following reason: Some Code Pages (which are determined by the Collation of each CHAR / VARCHAR field) allow for double-...


10

Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential performance impact to DML opertions (which actually was already there). There is very real impact to performance and functionality, and this change not only did not ...


10

While I am not sure of the exact reason for those specific characters, t The issue has to do with the older collations (please see UPDATE section at the end). And it is not just empty string that they equate to, but also to just one of those characters: SELECT * FROM (SELECT N'ግዜ') tab(col) WHERE tab.col = N'ግ'; And if you try a case-sensitive collation, ...


10

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 will need to read every data page from the table. With that in mind I think it makes a lot of sense to combine all of the column checks into a single query ...


10

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 data, and even for XML. Hence, ù is the 8-bit version of the two-byte UTF-8 sequence for ù (0xC3 and 0xB9). Fortunately, it is possible to convert a UTF-8 ...


9

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 ?. This is not a display issue, those characters are now physically a regular question mark. You will need to do a restore from a backup, unfortunately. The following example ...


8

Why does SQL convert Unicode 9619 to ASCII code 166? SQL Server is not employing any special custom logic here; it is using standard operating system services to perform the conversion. Specifically, the SQL Server type and expression service (sqlTsEs) calls into OS routine WideCharToMultiByte in kernel32.dll. SQL Server sets the input parameters to ...


7

This question is far more complicated than it appears to be on the surface (hence the longer-than-most-would-expect answer). If the strings being searched were codes (postal codes, ISO country codes, ISO state codes, SKUs, etc) or something where the characters used were a limited subset of all possible letters of all languages, then this would be fairly ...


7

First, as you have now seen, you cannot directly alter meta-data in the system views. However, you could change the setting for a particular database using ALTER DATABASE: ALTER DATABASE { database_name | CURRENT } COLLATE collation_name; Please note that the option to use the CURRENT keyword was introduced in SQL Server 2012. OR, if you only want to ...


7

Converting from Unicode data to a particular Code Page employs what is known as the "Best-fit" strategy (as noted in @Paul's answer and in the link that @Martin noted in a comment on the Question). According to that MSDN page for Character Encoding in the .NET Framework: Best-fit mapping is the default behavior for an Encoding object that encodes Unicode ...


7

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 by ASCII. SQL Server supports many code pages via collations. Non-ASCII characters can be stored in varchar as long as the underlying collation supports the ...


7

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 means you have a sneaky, invisible character right after "sql" instead of an innocent space character. It's not in your question, probably got lost in ...


7

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 means "UTF-16 Little Endian"). It is just a simple miscommunication. The source of the communication breakdown (it's always the same, right ;-) is merely not ...


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