How to escape special characters in MySQL When I do select * .. | mysql ... &gt; /tmp/file from a table with text, there are some problematic characters that prevent me from loading it to a different db using copy (postgres) or load into (mysql).
Character like tab, new line are translated automatically to \n and \t, but some of the problematic characters are escape ^[, CR ^M, ^U,^Z,^F,^H and maybe other that I haven't seen before.
Usually I would just replace it like echo &quot;select * ..&quot; | mysql .. | sed 's/\r/\\r/g', but there are too many unknown chars there. So instead of replacing them after the select I would like a function that retrieve the text already escaped (I guess remove them is also fine).
How should I do it?
To escape all special char except NULL, new line and tab:
select * .. | mysql ... | sed 's/[\x04-\x08\x0B-\x1F\x7F]/ /g' &gt; /tmp/file 
useful link:
https://stackoverflow.com/questions/6534556/how-to-remove-and-all-of-the-escape-sequences-in-a-file-using-linux-shell-sc