When I do select * .. | mysql ... > /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 "select * .." | 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?
Best Answer
To escape all special char except NULL, new line and tab:
select * .. | mysql ... | sed 's/[\x04-\x08\x0B-\x1F\x7F]/ /g' > /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