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 ...
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
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"; "הֶ" =...
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, ...
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 ...
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
insert into @...
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
For followers, since this seems to be the canonical question for "converting bytea to text" (i.e. so you can actually see it in pgAdmin etc.) . Here's how to just get it viewable:
select encode(table.your_column_name, 'escape') as some_name from table_name
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.
Have you tried the encode(data bytea, format text) with escape format. In that syntax format can be any of these,
So encode(E'123\\000456'::bytea, 'hex') will output the bytea as hex-encoded.
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-...
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 ...
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, ...
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 ...
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 ...
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 ...
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 ...
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
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 ...
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 ...
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 ...
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 ...
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 ...
The PostgreSQL documentation leaves a lot to be desired (just sayin' 😼 ).
To start with, there is only one encoding for a particular database, so C and C.UTF-8 in your UTF-8 database are both using the UTF-8 encoding.
For libc collations: typically collation names, by convention, are truly two-part names of the following structure:
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. ...
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)
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 ...
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 ...
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 ...
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 ...
Each column of each table of each database can have a different CHARACTER SET and COLLATION. There is no downside unless you JOIN on a column that has different charset and/or collation.
Ascii (7-bit stuff) is a subset of latin1 and of utf8mb4. As such, the encoding is identical, so no space difference will be seen for purely ascii text.
However, there ...