24

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process: Step 1: Create a backup Step 2: Upgrade the MySQL server Step 3: Modify databases, tables, and columns Step 4: Check the maximum length of columns ...


18

No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so: USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "your_schema_goes_here"; Then you can run the output from this to do what you ...


17

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


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


11

Good question. I'll give a simplified answer. Oracle supports two character sets simultaneously, by way of different datatypes and parameters. A "normal" database-wide characterset and a "national" characterset. Now, the "normal" characterset affects the way that VARCHAR2, CHAR and CLOB data is stored. The "national" characterset affects the way that ...


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


8

Think about it: You are storing data in the database as latin1 You are data is handled internally by mysqld as latin1 If data coming from the OS or from the connection is utf8, how is mysqld going to treat it? Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and ...


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

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


6

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes. By way of example, this query shows you ...


6

The documentation is not very clear with respect to the relationship between the encoding / character set of the database and the ctype / codeset of the collation. All that it mentions are the following statements (all found on the 22.3. Character Set Support documentation page): each database's character set must be compatible with the database's ...


5

CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed). VARCHAR occupies 1-2 bytes for a length, plus only as many bytes as needed. So VARCHAR(100) with hello will occupy 7 (2+5) bytes in any character set. Señor, in CHARACTER SET latin1, take 5 bytes (plus length). In utf8, it takes 6 bytes (...


5

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII ...


4

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".


4

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


4

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example: echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd 0000000: 3307 Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you ...


4

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" LC_MESSAGES="en_US....


4

You have to specify that you are inserting wide character data: CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX)); INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ'); INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ'); SELECT * FROM #t; DROP TABLE #t; Result:


4

I had the same problem with an Ubuntu installation of MySQL 5.6.23. I had to edit /etc/mysql/my.conf and add these entries to these sections: [client] default-character-set=utf8mb4 [mysqld] character-set-server = utf8mb4 [mysql] default-character-set=utf8mb4 Then as root execute: service mysql restart Both my webserver connections and my local (shell) ...


4

DBMS_LOB package has conversion functions as well. Unfortunately there is no support for converting CLOB to CLOB and change characterset in one step, so the data is first converted to BLOB, then back to CLOB. CREATE TABLE EXAMPLE (T CLOB); INSERT INTO EXAMPLE (T) VALUES ('Example β '); begin for i in 1..12 loop update example set t = t || t; end ...


4

Or are the only encoding types ever available listed in Chapter 23.3 Character Set Support? Yes. You can see the full list in the source code, if you insist That said, it's highly abnormal to use any of these encoding except UTF-8 as an internal storage format. Unicode should support all of the useful characters in all of the other encodings. If you find ...


4

But the definition of varchar says, it allows non-unicode string data. But the Trademark(™) and Registered(®) symbols are Unicode characters. Does the definition contradicts the property of varchar datatype? While the other answers are not incorrect, I think it would help to point out a confusion in base terminology. I have emphasized two words in the above ...


4

I don't think there is a way to change that DEFAULT. Anyway, it would be better to use utf8mb4_unicode_520_ci, which is based on a later Unicode standard. Just get into the habit of specifying CHARACTER SET and COLLATION on all connections and CREATE TABLEs. MySQL and MariaDB are gradually changing from latin1_swedish_ci to utf8mb4_0900_ai_ci. MariaDB is ...


4

Why is the accepted answer not working for char(150)? Actually, it does. The problem is your test is bad / invalid. You test column, DataColumn, is using NVARCHAR instead of VARCHAR. The character itself works in both datatypes, but the behavior is different due to how it is being used in each case: In the Find_Invalid_Chars() function (i.e. the "...


3

Easiest way: export the database. Open the exported database in a text editor and perform a Find/Replace with "UTF8" or whatever. Re-import amended database.


3

And to get your columns to DT_WSTR as liam.confrey mentions, you will want to click on your flat file connection manager and redefine each column type from string [DT_STR] to Unicode string [DT_WSTR] Do note that if you misclick like I usually manage to do and select something like 'two-byte unsigned integer [DTUI2]', classic off by one, the ...


3

since you confirmed the character set in the database is AL32UTF8, you need to make sure the fonts is installed on the Linux machine, and install the message libraries. that will make ORA_NLS33 has been set appear when you execute alter session set nls_language=


3

Assuming your source data contained only English and Greek characters, and was mis-exported with an ISO-8859-1 to UTF-8 conversion (rather than an ISO-8859-7 to UTF-8 conversion), you can get your data back by first repeating the missed conversion the other way around, and then doing the right one. You could use iconv for this (available on pretty much all ...


3

Do the Following things: Set the database charset to utf8mb4 Set the charset of column to utf8mb4 like below query: ALTER TABLE `comments` CHANGE `text` `text` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;


3

https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html describes the supported variations in table definitions. For one, it appears that this is supported with statement-based replication: When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute ...


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