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

user_514235 0
Viewed 10386

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?

sql-insert newline sqlite c++
2 Answers

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');";
user_116540

Specific to Qt

To make the Qn generalised, I didn't mention about Qt. For those who use Qt, the multi-line handling is done in following way:

void InsertToDB (QString contents)
{
  contents.replace("\n", "<br>"); // ... rest will be taken care by Qt
  sqlite.insert<wherever>(contents);
}

Note: We observe that even typing <br> in a text box of Qt, inserts a new line!

user_5142350
Related