How To Search For And Remove Line Feed Characters

[DennisM]

I am not proficient with MySQL at all. I have discovered that a number of records in my database contain either line feed or carriage return characters. I discovered this looking into the characters in my export file.

Why don’t I see any evidence of this when I look at the cell in SQLyog? The record looks fine in SQLyog even when the cell is clicked on to edit. If I copy the cell into Notepad I see the cursor go to the next line. If I reenter the data those characters are gone. I can paste the cell into Notepad and the cursor remains at the end of the line.

How can I search for and remove these LF or CR characters?

I have the Enterprise version of SQLyog.

Thanks,

Dennis

[ashwin]

Hi DennisM,

If you had copy pasted rows from Excel to SQLyog, then Excel is the source of r. When you copy paste a single cell value, r gets appended to the data. As a work around for this you can copy only the data with in the cell (instead of copying the entire cell) in order to avoid the r getting appended.

Quote:

Why don’t I see any evidence of this when I look at the cell in SQLyog?

Unlike a text/blob field which has multi line edit control, Varchar has a single line edit control because of which characters like r,n(non printable characters) are not visible.

Quote:

How can I search for and remove these LF or CR characters?

You can search for these characters by switching to Text view(Toggle between Grid Mode/Form mode and Text Mode by pressing Ctrl+L). See attached images(Grid and text view). But you cannot remove the characters from SQLyog. You can follow the workaround which I mentioned above to avoid the r getting appended.

Thanks,

Ashwin A

[DennisM]

Ashwin,

Thank you for your informative reply. I wasn't aware of the Text mode.

You are also correct about the copying from Excel, I updated numerous cells by cutting and pasting from a spreadsheet. I appreciate your instructions how to do it properly.

If I can't make the corrections from within SQLyog, can you suggest any other way of doing it?

It isn't a very large database, maybe 600 records but a lot of columns over 80. It's a club membership database. I hope to someday learn enough about relational databases to split it up into two, which would remove a tremendous amount of duplicated info.

Thanks again,

Dennis

[peterlaursen]

I would consider dumping the table, edit in a (HEX-)editor and import again. Search and replace all linebreaks followed by an apostrophe with just an apostrophe should do the trick if I understand.

Alternatively you may use an UPDATE statement using string functions (http://dev.mysql.com…-functions.html). Something like

UPDATE table SET column = LEFT(LENGHT(column)-1) WHERE column LIKE '%n';

.. this will (should – not tested!) remove the last character from the column if the last character is n (or CR in DOS/Windows terminology)

However I think edit dump+edit+import is faster and simpler in particular if all columns of the table need to be handled.

[DennisM]

Thanks Peter

I will try you dump+edit+import suggestion.

Dennis

[Author]

Posts