Hot answers tagged

17

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


16

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


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


6

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


6

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


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


4

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


4

After some trial and error, I've learned how and where to apply COLLATE: Converted lines like: SELECT SOMETHING FROM SOMEWHERE WHERE table_schema = given_database AND table_name = given_table AND index_name = given_index; To: SELECT SOMETHING FROM SOMEWHERE WHERE table_schema COLLATE utf8_unicode_ci = given_database AND table_name COLLATE ...


4

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


4

You must pass a locale that matches your desired encoding and is supported by the host as well. For example, if you don't want localised collations etc, you could pass --locale=C. Otherwise you might want --locale=en_US.ISO-8859-1 (or just en_US).


4

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


2

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


2

As you have correctly identified, Nadège is the UTF-8 representation of Nadège incorrectly decoded as ISO-8859-1 ("latin-1"). Then, in your case, re-encoded to UTF-8 for storage in the DB. To fix it you need to: Take the current representation and decode the UTF-8 to latin-1 as a byte string re-interpret the byte string, decoding it as utf-8 So: ...


2

Sometimes ago, I faced a similar issue and I solved that issue with the help of my friend. How I did was I changed the collation to uft8_turkish_ci in MySQL. Also from MySQL website, I found the following document, which may be helpful for you, MySQL Character set Thanks.


2

Are strings in table columns represented as bit patterns or Unicode? Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info. Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the ...


2

I suspect your database content may be in iso8859 or cp1252. If it were ascii, you would not run into problems importing it. You may be able to determine the coding by opening your dump with python. The following python3 tries encodings until it succeeds. It can be used to determine the file encoding. for enc in ('cp1252', 'utf8'): ...


2

The encoding defines the very basic rules how characters are represented in binary format (like @a_horse explains in his comment). It should be mentioned that the server encoding has to match the client encoding for successful communication. Postgres can translate if necessary, there is a dedicated setting client_encoding for this. The locale is a superset ...


2

The solution isn't precisely the same but this question is where I originally found direction for a similar issue and the concepts there should take you where you want to go. MySQL has a BINARY character set and from all appearances, by converting through it, you can prevent MySQL from realizing what you're actually doing and being "too helpful." Test case ...


2

PostgreSQL 8.4 supports only one locales that is selected in installation time. You need to rerun initdb statement with different locales (but only one can be used) or you can migrate to 9.x version, that supports more locales.


2

know that I should migrate my database to utf8 to solve this problem, but for some reasons, I can not do that for the moment. In my case, I'd rather PostgreSQL saves my string removing characters it can not convert or for example replacing them with some symbol like "?" rather than throwing an error... PostgreSQL does not support this. It's ...


2

psql detects the client_encoding from the LC_CTYPE variable in the environment; this falls back to LC_ALL and then LANG if unset. In the terminal you're launching psql from, run locale. e.g. $ locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" ...


2

Depending on what RDBMS you are using: You can try altering the Latin1-encoded column to use an encoding of UTF-8, if the RDBMS supports such an operation. If you are using MySQL, you should look at this related Question on S.O.: Converting mysql tables from latin1 to utf8 You can try: Adding a new column with an encoding of UTF-8 UPDATE new_column = ...


1

Interesting question. It looks like in second scenario you firstly convert default charset for table: To change only the default character set for a table, use this statement: ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name; So then you try to MODIFY charset of your xml column. But database may think that this column is already in the ...


1

On PostgreSQL 9.1 and newer, you can use the COLLATE qualifier on an operation to override the database's default collation. See the manual for information on collation support. E.g. SELECT a, b FROM mytable ORDER BY c COLLATE 'th_TH.UTF-8'; Note that PostgreSQL can't mix different collations, using a dynamic collation based on detected language. It ...


1

Have you tried the encode(data bytea, format text) with escape format?


1

Thank you very much - I found the solution. First, the output encoding of the Oracle command line tools can be controlled by either the NLS_LANG environment variable (in my situation, a value of GERMAN_GERMANY.AL32UTF8 was needed) or through the registry by the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG Thank you very much for all - I leave this ...


1

Since the DB has both Engliah and Thai I decided to change the collation for the columns. ALTER TABLE books ALTER thai_title TYPE varchar(255) COLLATE "C";


1

I found a solution, for everyone to know: Assuming that we put the xml field in a variable of type varchar(max) and then casting it to a variable of type XML (but not by using the CONVERT function, rather just by simple assignment), in the end the value of the newly created XML variable will be included in an insert query, by casting it back to ...


1

The backslash \ is the default escape character in the (default) text format of COPY. The format you describe is recognized as (quoting the manual here) backslash sequences are recognized by COPY FROM ... \xdigits | Backslash x followed by one or two hex digits specifies the character with that numeric code .. which seems to be just as intended. ...


1

You'll have to specify a character set of UTF8 on the table schema. See http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html Depending on your needs you can specify table defaults which then apply to all unspecified text columns (char/varchar/text) or you can specify on a per column level. You'll also need to have your applications to specify a ...



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