How to insert a new line ("\n") character in SQLite?

Question

While trying to insert something like:

"Hello\nWorld"

SQLite throws error something like:

Message: unrecognized token: "'Hello";" (also few other errors)

Even though I convert above string to "Hello''\nWorld" or "Hello\"\n\"World", these escape characters sequences don't work in this case.

Currently using C++ language, I am inserting this column like any other simple string columns. I tried above escape sequence (even though I read in internet that, they don't work with \n).

How to insert new line and other such special characters in SQLite DB?

Answer 1

In SQL, there is no mechanism to escape newline characters; you have to insert them literally:

INSERT INTO MyTable VALUES('Hello
world');

Alternatively, construct the string dynamically:

INSERT INTO MyTable VALUES('Hello' || char(10) || 'world');

(The type of newline (13 or 10 or 13+10) is OS dependent.)

When you embed the SQL statements in C++ strings, you have to escape the newline in the first case:

q1 = "INSERT INTO MyTable VALUES('Hello\nworld');";
q2 = "INSERT INTO MyTable VALUES('Hello' || char(10) || 'world');";