I have a db in which the data in some fields in some tables has newlines (line breaks). All is fine until I want to export this into a CSV or tab-delimited format in which case the line breaks mess up the row-by-row records. I am thinking, maybe I should not allow storing line breaks in the database (the fields are populated via a web application) and instead, I should store some other "restricted (by me)" character, and replace that character when I have to display the content of these fields. That would allow me to do exports of the entire db easily and accurately.
Question: Is there a way I can search and replace all the line breaks? So, given CREATE TABLE t (id INTEGER, essay TEXT); I'd like to ALTER TABLE t ADD COLUMN essay_without_newlines TEXT; UPDATE t SET essay_without_newlines = Replace(essay, '\n', '~'); where ~ happens to be my proxy for a newline One, I am not quite sure how to identify these newlines. A simple SELECT id FROM t WHERE essay LIKE '%\n%'; returns nothing at all. Since the values have been inserted via the web, I am not even sure if they are \n or \r\n (Unixy or Mac-y or Window-y). Suggestions? Or, any other way to handle this problem, perhaps more elegantly than what I am thinking of? Many thanks in advance, -- Puneet Kishor _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
