Questions tagged [utf-8]
Unicode variable-width character encoding (between 1 and 4 bytes per code point).
104
questions
216
votes
2
answers
551k
views
Create a MySQL database with charset UTF-8
I'm new to MySQL and I would like to know:
How can I create a database with charset utf-8 like I did in navicat?
create mydatabase;
...seems to be using some kind of default charset.
- 2,271
20
votes
1
answer
24k
views
Querying non-ASCII rows from Postgres
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.
I have a UTF-8 database, where collation and c_type are en_US.UTF-8, ...
- 367
19
votes
2
answers
54k
views
Set value of character_set_client to utf8mb4
I'm trying to convert my DB to utf8mb4 following this guide. I have set:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init-connect='SET NAMES utf8mb4'
...
- 295
14
votes
1
answer
17k
views
Encoding issue with SQL Server VARCHAR column retrieved in Python
We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server. In SSMS, the varchar appears as:
"Who Killed JonBen‚t?"
However, when it's brought ...
- 243
12
votes
2
answers
3k
views
When `nvarchar/nchar` is going to be used with SQL Server 2019?
With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says:
This feature may provide significant storage savings, depending on the
character set in use. For ...
- 3,770
12
votes
1
answer
48k
views
Why default character_set_server is latin1?
I am using MySQL 5.5 and when I show variables about charset, I have
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------...
- 549
10
votes
2
answers
8k
views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
10
votes
1
answer
18k
views
Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts
I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server:
mysql> show tables;
+-----------------------+
| Tables_in_blog |
+--...
- 405
9
votes
2
answers
3k
views
Why does Oracle use a different byte length than java for the supplementary unicode character chipmunk?
I have java code trimming a UTF-8 string to the size of my Oracle (11.2.0.4.0) column which ends up throwing an error because java and Oracle see the string as different byte lengths. I've verified my ...
- 211
9
votes
1
answer
30k
views
MySQL: Illegal mix of collations
I've tried using a stored procedure to create indices, and got the following error:
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,...
- 11.5k
6
votes
2
answers
15k
views
MySQL silently replaces UTF chars with literal question marks
I'm experiencing a situation similar to this SO question i.e. I'm working with a legacy database which has UTF8 content in latin1 tables (pretty ugly I know).
Now I'm getting new data from a new ...
- 169
5
votes
2
answers
6k
views
How to convert mysql database charset to utf-8
I have a vb forum with a huge amount of data
I need a way to convert this forum database tables charset with thier data from
latin1_swedish_ci to utf8-general-ci
How to do this ?
5
votes
1
answer
3k
views
What is the difference between different utf8mb4 binary collations?
What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?
- 398
5
votes
1
answer
501
views
postgresql matching or converting utf-8 variant strings
Postgres 13
I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql.
I am running into ...
- 153
5
votes
1
answer
7k
views
Postgres database encoding problem
I'm striving to convert badly encoded data from my table. For instance, I have a field with Nadège which should be Nadège.
I tried using Postgres's functions convert, convert_from, convert_to ...
- 153
4
votes
1
answer
581
views
Fix corrupted utf8 nvarchar value that was inserted without N prefix
Is it possible to restore the UTF8 data from a corrupted nvarchar, that was used without the N prefix?
For example, in the following snippet, I'd like @n1_fixed to have the correct value based on @n1:
...
- 437
4
votes
1
answer
791
views
What is difference between Arabic_100_CS_AS_KS_WS_SC_UTF8 and Latin1_General_100_CS_AS_KS_WS_SC_UTF8?
Starting from SQL Server 2019, it supports UTF-8 as collation. However, according to the following queries:
SELECT COLLATIONPROPERTY('Arabic_100_CS_AS_KS_WS_SC_UTF8', 'CodePage')
SELECT ...
- 143
4
votes
1
answer
11k
views
Storing special characters (German, French, Spanish) in SQL Server result in weird strings
I'm trying to store the German ß in SQL Server, but it results in ß.
For example "Gleimstraße" is stored as "Gleimstraße"
But this also happens for other characters:
"König-...
- 245
4
votes
2
answers
3k
views
Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?
I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my ...
- 809
4
votes
2
answers
19k
views
Tell MySQL to start using utf-8 encoding without `convert to`ing it
In a fairly unique situation, my team has ended up with UTF-8 bytes in a database that thinks the data is encoded as latin1.
At least, I'm 85% certain that this is the situation at hand.
For example,...
- 151
4
votes
2
answers
9k
views
Postgres: set client encoding default
When querying from certain tables, I'll often receive the error:
character with byte sequence 0xef 0xbf 0xbd in encoding "UTF8" has no equivalent in encoding "WIN1252"
Varying bytes, but you get the ...
- 143
4
votes
1
answer
4k
views
Encoding Debug UTF8 & Latin 1
We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2).
For example we have strings like 'WilcoxonÃ'.
We got this data into ...
- 443
4
votes
1
answer
8k
views
Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE
I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested ...
- 171
4
votes
1
answer
1k
views
How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-? [closed]
I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables ...
- 1,103
3
votes
2
answers
4k
views
SQL Server 2019 UTF-8 Support Benefits
I'm already quite comfortable with using COMPRESS() and DECOMPRESS() in an internal forum software for our company (Currently in SQL Server 2017), but trying to make the database as efficient as ...
- 31
3
votes
1
answer
5k
views
How large space will be occupied by mysql for a varchar utf8 column?
Tip: To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible length....
- 319
3
votes
1
answer
4k
views
Are utf8_bin and utf8_general_cs the same?
Here's the situation:
I have come across several posts for "utf8_bin vs utf8_general_ci".
Similarly, "utf8_general_cs vs utf8_general_ci".
But I can't find a post for "utf8_bin vs utf8_general_cs". ...
- 644
3
votes
1
answer
11k
views
CONVERT() returns garbage with CLOBs in Oracle
I am looking to repair a table of data in Oracle, ideally via unprivileged SQL, which has had UTF-8 data inserted into a UTF-8 database, using the Latin-1 character set by mistake.
The symbol β GREEK ...
- 155
3
votes
2
answers
55k
views
How to convert a MySQL database from utf8mb4 to utf8?
I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_cicollation, to utf8 and utf8_unicode_ci.
How to do that please?
Thank you in advance for your ...
- 131
3
votes
1
answer
3k
views
Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG?
For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show ...
- 31
3
votes
1
answer
977
views
How can I disable utf8mb4 entirely on MySQL 5.5?
I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported ...
3
votes
2
answers
2k
views
MySQL What charset/collation for Case insensitivity and Accent sensitivity?
I am looking for a charset/collation that would make it so when I do a
SELECT * FROM table_name WHERE username = "Warrior"
It only returns me the rows where username = "Warrior", ...
- 167
3
votes
3
answers
12k
views
MySQL accent insensitive word search
After spending almost all of my day searching a fix to this I come to you guys.
I need to do an accent insensitive word search on my db(InnoDB), but I cannot get it to work.
I have t1 table, the ...
- 83
3
votes
1
answer
1k
views
Is there any danger using binary data over an UTF-8 MySQL connection?
My application uses UTF-8 as the default encoding for all fields, and the connection itself; every connection starts with:
SET NAMES utf8;
I need to store a few SHA-1 hashes though, and for ...
- 698
3
votes
1
answer
5k
views
Why pgAdmin4 invoke "'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte" error?
I'm newbie in postgreSQL ,when I'm trying to create a new local server using pgAdmin4 ,it shows me this error :
'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte
I'm ...
2
votes
1
answer
3k
views
UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables
The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings.
...
- 123
2
votes
2
answers
1k
views
Which accented characters are considered the same?
In MySQL, certain characters are considered the same when performing searches, e.g. 'n' and 'ñ'; unless you specify BINARY in the condition.
Is there a list somewhere of the characters that are ...
- 155
2
votes
2
answers
4k
views
How do I find an invalid utf8 character "somewhere" in my MySQL/trac database
I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently ...
- 243
2
votes
1
answer
742
views
Postgres encoding issue - find values that have no equivalent in (client) encoding
I have Postgresql database that internally uses UTF-8 encoding. Some clients that connect to the database use LATIN 2 (ISO 8859-2) client encoding when they connect to database. I can't change the ...
- 123
2
votes
1
answer
589
views
mysqldump shows pairs of utf8 chars when dumping a utf8 database
I'll describe right away the problem, and put details at the bottom.
I am dumping a database which I am fairly sure contains (almost: a few cells have wrong, latin1 encoded data) only UTF8 data. Also ...
- 145
2
votes
1
answer
360
views
How can I detect failure in CONVERT( tbl AS utf8)?
MySQL provides the CONVERT(`tbl` AS utf8) expression to transcode text from one charset to another.
Sometimes that conversion cannot succeed, because the destination charset does not include ...
- 191
2
votes
1
answer
3k
views
Impact of switching a column's encoding from utf8 to utf8mb4 in MySQL
Background Info
Let's say I have a table in MySQL that has +1 billion records. I have a column with that contains some description of a post. We don't index, search, or sort this column in any way.
...
- 131
2
votes
1
answer
1k
views
presentation of non-ascii characters (ñ)
I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.
What kind of setup do I need to perform at the ...
- 291
2
votes
1
answer
3k
views
Remove question marks from database(latin1_general_ci table)?
How can I remove question marks(not diamond shaped) from a latin1_general_ci MyISAM table?
I tried going back to utf8 and tried to apply things in https://stackoverflow.com/questions/1476356/...
- 71
2
votes
1
answer
89
views
CHAR and VARCHAR lengths for multibyte characters
I have a table with ~100mln rows. One of the columns is a VARCHAR(64) with utf8mb4 encoding for storing user nicknames (others are few integers).
The data in that column has MAX(LENGTH()) == 42 and ...
- 23
2
votes
1
answer
409
views
How can I detect double-encoded MySQL columns and rows, and validate the repair?
My database provider, bless their hearts, migrated our MySQL databases to another server recently, and introduced double-encoding of UTF-8 data via Latin1 into our text data. Strings like 'emdash—here'...
- 191
2
votes
2
answers
4k
views
How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?
I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
- 21
2
votes
0
answers
1k
views
Convert large utf8 database to utf8mb4
I have a large database (~450gb) which I need to convert from utf8 to utf8mb4.
I'm aware of the steps I need to take, but is this practical on tables containing this much data? I'm mainly concerned ...
- 188
2
votes
1
answer
7k
views
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
I have a databases with latin1 charachter set, I need to change tables character set to utf8, but I get error. the current character set is :
mysql> show variables like "%character%";
+-----------...
- 289
2
votes
1
answer
158
views
Find efficient way of migration 2TB GBK table in SybaseIQ to UTF-8 files
My project need to migrate more than 2TB raw data from SybaseIQ to another Database. SybaseIQ using GBK, the target Database using UTF-8, the target database can only load from UTF-8 files.
We use ...
- 131