Questions tagged [encoding]

Encoding is a set of rules used to represent data in a form that can be stored and transmitted to another process or system. Character encoding (e.g. Windows-1252, ISO-8859-1, UTF-8, UTF-16) refers to the way character data is represented as a series of bytes. Binary encoding (e.g. Base64) refers to the way binary data is transformed into a series of characters.

Filter by
Sorted by
Tagged with
1
vote
1answer
61 views

Why can't binary data be inserted/displayed as ones and zeroes?

If I have a column of type binary or varbinary, I imagine the data as a sequence of bits. For example, it makes sense to me that 01001 (as a base 2 number) could be a valid value in a binary(5) column....
9
votes
3answers
327 views

Is it possible to use OPENROWSET to import fixed width UTF8 encoded files?

I have an example data file with following contents and saved with UTF8 encoding. oab~opqr öab~öpqr öab~öpqr The format of this file is fixed width with columns 1 to 3 each being allocated 1 ...
0
votes
0answers
19 views

Convert any string to url valid percent encoding in BigQuery

I am trying to convert any string with any set of special characters into a valid url of the format below. In Bigquery Example: /artwork-v2/-̴̕ι-̶͔͛n̴e̷p̸u̴̒n̵uś̵̥o̵̙̾rt̷͗um̶̹͐-20380 encodes to: /...
-1
votes
1answer
36 views

How can I know if there is data loss when converting mysql character set(s)?

I'm converting a large (70G) legacy mysql database that is mostly utf8 (but with a sprinkling of other encodings in fields) to one that is (as uniformly as possible) utf8mb4 (utf8mb4_unicode_ci). The ...
1
vote
1answer
139 views

What is the impact of converting latin1/latin1_swedish_ci to utf8mb4/utf8mb4_unicode_ci?

I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is if this conversion is safe? Or does ...
2
votes
1answer
69 views

Unable to enter characters in MariaDB

I have just switched from MySQL to MariaDB and am running into a very silly problem: I cannot enter any extended characters in the database (for example, ö ä or å). The system is utf8, and I tried ...
5
votes
1answer
610 views

Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

I like to find missing indexes on the go, looking at the execution plans! It can potentially give me an indication where further to look at if I want to improve something that is currently running. ...
2
votes
2answers
514 views

How to create a new column in SELECT CASE when string contains Arabic words?

I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with ????? because I have set the column to Arabic words, I ...
3
votes
1answer
110 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
1
vote
1answer
258 views

mysqldump dumps different data with and without --no-create-info

mysqldump dumps different representations of data when called with/without --no-create-info. Test case First, create the test table and populate it with interesting data. CREATE TABLE `test` ( `...
0
votes
1answer
282 views

How to fix double-encoded UTF8 characters in postgres

I have a dataset (shapefile) with the same problem as the post below: https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table "A previous LOAD ...
0
votes
1answer
450 views

PostgreSQL pg_dump -E encoding option not working

I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-...
1
vote
1answer
323 views

How to escape special characters in MySQL

When I do select * .. | mysql ... > /tmp/file from a table with text, there are some problematic characters that prevent me from loading it to a different db using copy (postgres) or load into (...
1
vote
0answers
208 views

PSQL console encoding

How can I have a working PSQL console using UTF8 encoding under Windows? I have a Windows server and client. The Postgres 12 database contains tables with content in multiple languages (ex: English, ...
4
votes
2answers
417 views

Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
1
vote
1answer
6k views

Saving images as base64 encoded strings, why is it bad?

I've seen this on one of the production databases I've come across and these images apparently cover a large portion of their DB. After researching a lot I couldn't really find a lot of good answers ...
0
votes
1answer
2k views

Compress JSON String Stored in PostgreSQL, such as MessagePack?

JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string: {"0.000295":1544.2,"0.000324":1050,"0.000325":...
1
vote
0answers
130 views

MariaDB REGEXP_REPLACE Invalid utf8 byte sequence

I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (...
1
vote
2answers
684 views

Oracle to T-SQL OPENQUERY special character conversion issues

I'm struggling to figure out where the character encoding issue on my Linked server may be coming from here. The ZPDT_PAT_ALPHA column should have a degrees symbol at the end, as shown by the DUMP. ...
2
votes
2answers
59 views

charindex thrown off by extended characters

I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) ) This works ...
2
votes
0answers
61 views

Create/Edit SSIS Derived Column Task From Text/Code for Large ETL

I have to insert a large UTF-8 encoded flat file to a 1252 encoded sql server. In order to do so, I am using SSIS to type cast each column. Is there a way, other than copy pasting through all 400 ...
6
votes
1answer
5k views

Query to find rows containing ASCII characters in a given range

I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios. I would have asked my question on the original How to check for Non-Ascii Characters post, ...
4
votes
2answers
619 views

Different characters, same ASCII code?

I have this query that throws two results: SELECT id FROM table1 WHERE id like 'nm041033%' nm0410331 nm0410331 And this slightly different query that throws only one result: SELECT id FROM table1 ...
1
vote
1answer
2k views

Is there a MySQL character set and encoding that will allow for both emojis and accents?

I've got a database of terms that get added to by one group of users, and queried against by another. I was running into problems when people would query for an emoji in the database and my React app ...
2
votes
2answers
1k views

Handling data encoding issues while loading data to SQL from Script (Notepad++)

I'm pretty sure this is not a SQL Server problem. I already asked a question HERE with an awesome explanation, BUT I still can't explain to the guys where I work that it has nothing to do with SQL ...
9
votes
3answers
11k views

PostgreSQL: difference between collations 'C' and 'C.UTF-8'

In PostgreSQL, what is the difference between collations C and C.UTF-8? Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or ...
0
votes
1answer
5k views

How can I search for a hex string in oracle?

There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ): Thcˇodore And when I look at the record via SQL results, I see: Thc\xCB\x87odore I'...
5
votes
2answers
1k views

Byte ordering for multibyte characters in SQL Server versus Oracle

I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration. Environment Details: Oracle 12 - AL32UTF8 ...
1
vote
2answers
5k views

Bulk insert not retaining special chars of UTF-8 Encoded txt file Sql Server 2008

I have a stored procedure that bulk imports a text file and inserts it into my database. CREATE TABLE DBO.TEMP_STORE ( ID nvarchar(max), [MONTH] nvarchar(max), [...
4
votes
3answers
899 views

Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database?

I'm building a new product for toponyms and in it the Arabic shows kinda like this: ^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$ Actually not quite. This is a ...
2
votes
1answer
2k views

How to insert a Unicode character verbose into a varchar DB?

I need to insert this character '●' into a VARCHAR column of a MSSQL database with collation set as SQL_Latin1_General_CP1_CI_AS (or at least mock what my Python + Windows MSSQL Driver might have done)...
3
votes
2answers
3k 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 ...
0
votes
1answer
56 views

Users running ANSI scripts are having problems with special characers [closed]

Long story short. How can I fix users environments, to make them run our scripts using ANSI encode? The problem is, we send them scripts to run on their databases using ANSI encode. But some of ...
17
votes
1answer
8k views

Error starting SQL Server 2017 service. Error Code 3417

I have SQL Server 2017 installed on my computer. This is what SELECT @@VERSION returns: Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) ...
0
votes
1answer
1k views

How to configure DBeaver to save files without a BOM for UTF files?

I am looking for the equivalent setting as the PGADMIN setting Write BOM for UTF files which will cause saved files to be saved with/without the BOM. I've found settings in DBeaver when exporting ...
1
vote
2answers
1k views

Wrong sort order using utf8_unicode_ci and swedish characters like ö

I am confused about what encoding and collation I should use so my queries returns the correct sort order. Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and ...
1
vote
1answer
26 views

Table/Column Character Encoding and Indexes

Does table/column character encoding have any impact on how the MySQL optimizer chooses join indexes? I have a table that have the same columns and indexes on two servers, one server does a eq_ref ...
4
votes
1answer
13k views

Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?

I've been looking at how to decode Base64 with SQL Server and after searching around many solutions online (some from here) seem to be based upon this sort of method. SELECT CAST(CAST('...
1
vote
1answer
81 views

Convert Character Encoding, and Check Encoding for Issues

I'm planning to convert a table from utf8 (_general_ci) to utf8mb4 (utf8mb4_unicode_ci). I want to make sure I don't have any malformed data after the conversion though so I was planning to duplicate ...
11
votes
1answer
6k views

Postgres - Encoding, Collation and CType

List of databases Name | Owner | Encoding | Collate | Ctype | -------------------------+----------+----------+---------------------------...
3
votes
2answers
1k views

Use non-printable characters in SQL Server BCP xml format file as TERMINATOR

I have a flat-text data file containing records which fields are separated by the non printable character "File Separator" (0x1c). I am trying to use SQL Server's bcp utility to load this data into my ...
2
votes
3answers
9k views

NLS_LANG and NLS_CHARACTERSET

I thought I read that NLS_CHARACTERSET has the encoding of the database and NLS_LANG the encoding of the client. Is that correct? Does that mean that the two encodings can be different? In some ...
0
votes
1answer
182 views

Change of NLS_LANG

Until not long ago, the NLS_CHARACTERSET and the NLS_LANG were both ISO88591. We deliver to production some scripts encoded in ISO88591. Since they changed their NLS_LANG in utf8, they have a problem ...
2
votes
1answer
427 views

Is there any string compaction option for PostgreSQL?

I have a table like this: CREATE TABLE BLOG( CATEGORY CHAR(32) ); Category is a column, which stored enum values. For example, I have 1 millions of rows and 10 unique values of Category. Let'...
2
votes
2answers
1k views

DB2 on Windows: Fix encoding in db2cmd and for JDBC

I'm running a DB2 v10.5 FP 7 on a Windows Server 2013 and there is a problem with the encoding setup: The windows is set to use German(Switzerland) as region setting and input language. chcp: Active ...
3
votes
2answers
11k views

Character Conversion Issue Importing From CSV File

After doing a load of CSV files, there are various words that are "written" to the database incorrectly. Some examples: Diã¡ria should be Diária Crã©dito should be Crédito Ligaã§ãµes should be ...
5
votes
2answers
7k views

SQL Server Bulk Insert properly interprets some Unicode characters but not others?

For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters: C9 (É) into 2B (+) A1 (¡) into ED (í) A0 ( ) into E1 (á) AE (®) into AB («) CB (Ë) into 2D (-) D1 (...
1
vote
0answers
91 views

Merge Replication Business Logic Handler Incorrect Encoding when connecting to SQL Server

We have a SQL Server Merge Replication configuration that uses a business logic handler to do conflict resolution on a datetimeoffset column. All we're doing is a copy of the Subscriber or Publisher ...
5
votes
2answers
4k views

Why is my UTF-8 document raising UTF-8 encoding errors in Azure Data Lake Analytics?

I have a document that was compressed in gunzip from a unknown source system. It was downloaded and decompressed using a 7zip console application. The document is a CSV file that appears to be encoded ...
2
votes
2answers
4k views

How to Create a PostgreSQL database with different Encoding?

I want to create a New Database that will store data in German(i.e Text with accents etc). Hence I tried to create it using the following SQL: CREATE DATABASE testdb WITH ENCODING='LATIN1' ...