Skip to content
Advertisement

Compare and trim binary/unicoded string to normal string?

I am using below mysql query to check which records vary from the trimmed value

SELECT id, BINARY(username) as binary_username, TRIM(username) as trim_username FROM table.

Above query returns binary value and trimmed value as shown below.

Result of mysql query:

mysql-query-result

Highlighted values in above image show that binary value vary from trimmed value.

I tried below 2 things:

  • calculating length of both binary and trimmed column but it is same LENGTH(binary_username) != LENGTH(trim_username).

  • equating them directly binary_username != trim_username.

but both of them are returning empty records.

How can I fetch these highlighted entries using mysql?

Edit 1: I have added HEX value in the query result

SELECT id, BINARY(username) as binary_username, TRIM(username) as trim_username, HEX(username) as hex_username FROM table

query-result

Thanks in advance…

Advertisement

Answer

To avoid storing, trimming, etc, the trailing zeros, use VARBINARY instead of BINARY. Why, pray tell, are you using BINARY for text strings??

Please do SELECT HEX(username) FROM ... so we can further diagnose the problem. That screenshot is suspect — we don’t know what the client did to “fix” the output.

Well, none of those are encoded in UTF-8, nor anything else that I recognize. The ‘bad’ characters (02, 04, 0c 17), are all “control codes” in virtually all encodings. (“Unicode” is not an encoding method, so it is not relevant.)

Would you like a REGEXP that tests for control codes?

In PHP, json_encode has an option for JSON_UNESCAPED_UNICODE. See https://www.php.net/manual/en/function.json-encode.php

But that generates u1234 type text.

When storing binary data into MySQL, use the binding or escaping mechanism in PDO or mysqli.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement