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 ...
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 (assuming your_column_name is
select encode(table.your_column_name, 'escape') as your_alias_name from table_name
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.
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 all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
insert into @VarcharUnicodeCheck ...
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 ...
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.
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-...
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
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 ...
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, ...
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 ...
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 ...
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:
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 ...
The problem is that you are converting XML data, which is stored as Unicode (UTF-16 specifically), into 8-bit data. 8-bit data requires a code page to know which set of up to 256 characters to use (or up to approximately 24k characters if using a Double-Byte Character Set, but the vast majority of the time it's 256 characters max). The issue here is that ...
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 ...
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 ...
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 ...
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 ...
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 ...
Since you are using Unicode / UTF-16 (i.e. NVARCHAR), you do not need either the CAST or even an Arabic_* collation. The only requirement for string literals is to prefix them with a capital-"N":
SELECT 'قدوم' COLLATE Arabic_CI_AS, '-o-' AS [-o-], 'رجوع' COLLATE Arabic_CI_AS;
-- ???? -o- ????
SELECT N'قدوم' COLLATE French_CI_AS, '-o-' AS [-o-], N'...
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 ...