13

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?

iammilind
  • 68,093
  • 33
  • 169
  • 336
  • How about `"Hello\\nWorld"`? It also depends on how you express this in code. Better if you could post a code that reproduces the issue. – Nawaz Nov 10 '17 at 16:59
  • @Nawaz, actually for now in my code, I am simply inserting this new string like any other string and it's failing. It's possible that, the way I am converting the string could be wrong as well. I haven't tried your solution, let me see if it works. – iammilind Nov 11 '17 at 04:14

2 Answers2

18

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');";
CL.
  • 173,858
  • 17
  • 217
  • 259
0

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!

Community
  • 1
  • 1
iammilind
  • 68,093
  • 33
  • 169
  • 336