1

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?

Improve this question
3
  • can you provide more content like the text you try to import and the table, the are to load into some more possibilities like ESCAPE – nbk Nov 24 '20 at 17:05
  • Execute SELECT .. INTO OUTFILE instead of simple SELECT. – Akina Nov 24 '20 at 17:34
  • Avoid shell scripts -- that just doubles the complexity. Go through files. – Rick James Nov 28 '20 at 0:08
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.