How to remove newline characters from data rows in MySQL?

Last few days I have trouble with one of my production app.

The trouble was I could not get my data through the app even though the data was recorded in the database.

To give you a context I will explain my table structure.

My app saves user’s data from several devices, So I need to save the id and device_id to know from which device the data was recorded. It is clear, to get John data I can use this SQL query

SELECT from users WHERE id = 123 and device_id = "JDAHADNW"

Unfortunately, I did not get anything. When I check the device_id values I found that after “JDAHADNW” there was a new line character (enter).

So I need to remove the newline character (enter) to get my previous query to work properly.

A further problem is that the rows that experience something like this are not only 1 but hundreds, so I need an instant solution that can remove all enter characters in the device_id column.

This is what I did to solve my problem

UPDATE sampling SET device_id = TRIM(TRAILING '\n' FROM device_id)

However, if the newline character is at the start of the field you can use this query instead

update mytable SET title = TRIM(LEADING '\n' FROM title)

Finally, my problem solved!

Source: https://stackoverflow.com/questions/1504962/how-to-remove-new-line-characters-from-data-rows-in-mysql/1504982

If you have trouble with Laravel Specified key was too long error you can check this out: https://tech.digitindo.com/2021/01/25/laravel-specified-key-was-too-long-error/