38

Another option, one which I've just learned, comes from the sqlcmd documentation. You need to set the codepage for sqlcmd to match that of the file encoding. In the case of UTF-8, the codepage is 65001 so you'd want: SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log -f 65001


36

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 ...


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"; "הֶ" =...


17

SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT). The problem here is that your code is mistranslating that 0x82 character, ...


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 @...


15

I would start by saying that you are running SQL Server 2017 on Windows 10 (client OS) which is not supported see Hardware and Software requirements for SQL Server 2017. You are wasting the capability of enterprise edition and lot of money by running it on client OS. Plus I believe such issues are mostly bugs so I would suggest you to apply latest SQL ...


13

No, there isn't. SQL Server doesn't support UTF-8. You need to define your columns as nvarchar/nchar if you want unicode data. Note, internally SQL Server stores this as UCS-2. Note that this has ben requested from MS on Connect and there is an older KB article. And some info on this blog too


10

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows) See the manual for details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713


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

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

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N. DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6' SELECT HASHBYTES('SHA1', N'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT, HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT This will show matching hashes. NVARCHAR_INPUT ...


9

There are two possible questions here, and they have two different answers -- How do I make all new tables utf8mb4 It can be done (for one database) while creating a database: CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; You can ALTER a database (similar syntax), but that only provides a default ...


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 ...


9

This is an incorrect encoding issue. The characters are coming in encoded as DOS Code Page 850 yet the target Code Page you are using (based on the Latin1_General Collations) is Windows Code Page 1252. For example, in DOS Code Page 850, the Ç character has a value of 0x80 (or 128 in Decimal). However, that same value of 0x80 in Windows Code Page 1252 gives ...


9

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 ...


8

Have you tried the encode(data bytea, format text) with escape format. In that syntax format can be any of these, base64 hex escape So encode(E'123\\000456'::bytea, 'hex') will output the bytea as hex-encoded.


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 ...


8

I know that encoding affects how the information is actually stored (IE whether 'A' requires one byte or multiple bytes, and what value those bytes have, depend on the encoding). Yes. Encoding is the character-to-byte conversion algorithm. For mono-byte encoding such as LATIN1, it's trivially byte value = character number, but for UTF-8 it's more ...


7

As from the comments, the problem is not exactly with the table or the way SQLCMD imports the special characters. Usually, problematic imports are related to the format of the script itself. Management Studio itself offers the option of saving with a specific encoding, which should solve the problem in the future. When saving a file for the first time (or ...


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

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 ...


6

Converting BYTEA to TEXT requires you to know the internal encoding of the text. Without knowing the encoding, there is nothing you can do. In a normal text column, the database stores the text as whatever SERVER_ENCODING is set as. For instance, in your example \n gets translated into \012. Well, that's a property of encoding. It's not objectively true for ...


6

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 non-printable characters that are interpreted only by the font / rendering system. These two characters in particular are used to control this (see original ...


5

Well, the trick is that a database can only specify which "locale" it is used for at creation time. When you create a database you either specify what you want by specifying the codeset, territory and collation (example CREATE DATABASE MYDB AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/dbdir' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM), or you let ...


5

The file encoding for the attachments is standard UTF-16 Little Endian (LE) -- one of the various Unicode encodings. The first two bytes, shown in the first image, are FF FE. These are the Byte Order Mark (BOM), which is an explicit indication of the files encoding. And you can see in the chart on that linked Wikipedia page that FF FE indicates an encoding ...


5

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 complicated nature of text encoding, especially non-Unicode encodings. Everything is just bytes. What we see on the screen is just an interpretation of those bytes. ...


5

there are various words that are "written" to the database incorrectly. No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the characters are being written incorrectly to the data file. Either way, SQL Server is doing exactly what it is being asked to do. This is a simple encoding issue. The ...


5

It seems, from the documentation about BCP - Specify Field and Row Terminators (SQL Server), that non-printable characters are not supported: Characters Supported As Terminators The bcp command, BULK INSERT statement, and the OPENROWSET bulk rowset provider support a variety of characters as field or row terminators and always look for the first ...


5

trying to make the database as efficient as possible There are at least two different types of efficiency that are really at play here: space (disk and memory) speed Under certain conditions (as described in Outman's answer, which is a copy/paste of the "Recommended Uses / Guidance" section of my blog post, linked at the top of that answer) you can save ...


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