Database Administrators
Q&A for database professionals who wish to improve their database skills
Latest Questions
-1
votes
2
answers
1521
views
Error on updating a field with binary values
I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below: ``` UPDATE old_table t1 INNER JOIN new_table t2 ON...
I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below:
UPDATE old_table t1 INNER JOIN new_table t2 ON t1.id = t2.id
SET t2.message = CAST(CONCAT("0x",HEX(t1.message)) AS BINARY);
But after executing this my message field on the new table becomes "0x1E395565"
If I do this script below, it will show the real message. But doing this for a million of records will take too long to execute hence the UPDATE INNER JOIN:
UPDATE new_table SET message = 0x1E395565 where id =1;
How to update this correctly? I concat "0x" on the HEX(t1.message) because when I do a mysqldump --hex-blob and charset=BINARY. There are 0x on the values.
My data type for message field is VARCHAR(1000) I know this might be wrong but is there any workaround without altering the table.
JRA
(137 rep)
Feb 18, 2021, 01:48 PM
• Last activity: Mar 15, 2025, 08:01 PM
0
votes
0
answers
70
views
How to Convert Hexadecimal Text Fields to Readable Text in ibd2sql Output?
I’m using ibd2sql to recover data from MySQL InnoDB .ibd files. While extracting the data, I noticed that some of the text fields are being output as hexadecimal values like: 0x53205265736f6c76696e67204572726f728180 These fields are originally human-readable text (e.g., TEXT, LONGTEXT), but they are...
I’m using ibd2sql to recover data from MySQL InnoDB .ibd files. While extracting the data, I noticed that some of the text fields are being output as hexadecimal values like:
0x53205265736f6c76696e67204572726f728180
These fields are originally human-readable text (e.g., TEXT, LONGTEXT), but they are showing up as hex in the resulting SQL dump.
My questions:
- How can I ensure that text fields are extracted as readable text rather than hexadecimal?
- Could this be an encoding issue (e.g., UTF-8 vs. Latin1)? If so, how can I handle or specify the encoding properly with ibd2sql?
- Are there any tools or methods available to convert these hex values back to their original text representation?
**UPDATE:**
I used -D for debug and get following error in conversion:
[2024-10-20 12:45:49] [DEBUG] BLOB ERROR 'utf-8' codec can't decode byte 0x80 in position 15: invalid start byte
Any guidance would be appreciated. Thanks in advance!
Mahdi Talebi
(1 rep)
Oct 20, 2024, 07:02 AM
• Last activity: Oct 20, 2024, 09:27 AM
0
votes
2
answers
4683
views
Convert hex string representation to hex number in SQL Server T-SQL
I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-SQL that will convert the string '16A1', or a modified version of it, into the hex number `0x000016A1`?
I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-SQL that will convert the string '16A1', or a modified version of it, into the hex number
0x000016A1
?
Tim
(545 rep)
Mar 24, 2023, 11:44 AM
• Last activity: Mar 24, 2023, 04:41 PM
0
votes
1
answers
805
views
Covert from literal HEX value to INT MariaDB
I have a situation where i need to get the integer value from a literal hex. Example: "ff" equals 255 in decimal, but if i use the HEX("ff") function it returns 6666. Is it possible to get it to return 255? Like a=10, b=11... and so on Thank you
I have a situation where i need to get the integer value from a literal hex.
Example: "ff" equals 255 in decimal, but if i use the HEX("ff") function it returns 6666.
Is it possible to get it to return 255? Like a=10, b=11... and so on
Thank you
Adelina Andreea trandafir
(3 rep)
Jun 5, 2022, 02:01 PM
• Last activity: Jun 5, 2022, 02:57 PM
0
votes
2
answers
2023
views
64 bit hexadecimal AS Date in SQL
I am loading a Qlikview CalData PGO XML file into a SQL table. Dates in this XML are stored as a 64 bit HEX string and I need to convert it to a date. How do I do that? ### Example `40e58f2c4153d0f9` should be converted to `18/11/2020 09:11:29` I have seen some sample code within qlikview, which is...
I am loading a Qlikview CalData PGO XML file into a SQL table.
Dates in this XML are stored as a 64 bit HEX string and I need to convert it to a date.
How do I do that?
### Example
40e58f2c4153d0f9
should be converted to 18/11/2020 09:11:29
I have seen some sample code within qlikview, which is
daystart(date($(HEX64CONVERT(ToBeDeleted))))
mouliin
(530 rep)
Nov 25, 2020, 12:43 PM
• Last activity: Nov 25, 2020, 02:46 PM
Showing page 1 of 5 total questions