How to normalize whitespace for user-generated data imports in MySQL/PHPGet the solution ↓↓↓
I am building a web app that allows users to import CSV files into the database. It parses those uploads into string values. I had a case where I uploaded two different files that had the string "New Hartford". When I ran a SELECT statement to get all instances of this string, I noticed that what appears to be an identical string value results in pulling up different rows in the query results:
SELECT * from divisions WHERE name = 'New Hartford'; SELECT * from divisions WHERE name = 'NewВ Hartford';
Upon further investigation I found that it was the whitespace character between "New" and "Hartford". Apparently there is something different about one of them. I assume it is a character encoding issue, a control character, or perhaps a different form of a whitespace character. Two questions:  How can I detect these variations either before or after I insert the data?  How can I normalize white-space characters efficiently and elegantly before or during insert? (I'd ideally like a quick function in the insert statement itself, I think.)
For extra context, I am using MySQL Server 8.0, with the following charsets and collations:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; [result] character_set_client utf8 character_set_connection utf8 character_set_database utf8mb4 character_set_filesystem binary character_set_results utf8 character_set_server utf8mb4 character_set_system utf8 collation_connection utf8_general_ci collation_database utf8mb4_0900_ai_ci collation_server utf8mb4_0900_ai_ci
Share solution ↓
Link To Answer People are also looking for solutions of the problem: failed to create image decoder with message 'unimplemented'
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.