Hot answers tagged character-set
8
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 ...
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 ...
4
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 ...
3
Your database character set is WE8ISO8859P1, it doesn't have the € sign. Either use NVARCHAR2 or change your database character set (export, reinstall with a character set that supports the euro sign like WE8ISO8859P15 or AL32UTF8, and import).
Here's an example of NVARCHAR2:
SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET';
...
3
By default MySQL server is using latin1 character set for each incoming connection. Latin1, as you might know, does not support cyrillic symbols.
The simplest solution is to switch so called 'connection character set' by running SET NAMES 'utf8'; in the beginning of each connection.
For example, this query should work:
SET NAMES 'utf8';
CREATE TABLE ...
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
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 ...
2
I had the same issue with a migration from 9i to 11g. I decided that I did not want to take a chance that Oracle's character set conversion would do something I did not expect. Managers don't care about character sets, they just want to know that the data was migrated without any problems. I kept the WE8MSWIN1252 character set and there were no problems.
...
2
Opinion
It's probably better to throw an exception during your applications input checking and not pass the buck to the database.
Workaround
There is a "workaround" but your mileage may vary:
http://forge.mysql.com/worklog/task.php?id=3780
Brute Force?
You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the ...
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
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 ...
2
You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.
To see the current setting for the general log (location and whether it's enabled):
mysql> SHOW VARIABLES LIKE 'general_log%'; # shows the ...
1
How much data do you have? You could compare length and char_length of data values to return all the multibyte data, but if you have a ton of data, this will be too much to visually process.
Start with something like:
select [Column] from [Database].[Table] where length([Column]) != char_length([Column])
And use some OR statements in the where clause.
...
1
The command
ALTER TABLE ADD FULLTEXT INDEX index (column ASC);
sounds like it does not make sense.
What does FULLTEXT indexing a column in ascending order mean?
You should run it like this:
ALTER TABLE ADD FULLTEXT INDEX index (column);
Give it a Try !!!
1
No I don't believe there is. 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 ...
1
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
