How to make work newline inside c-string returned by a sqlite3 text column?

Question

I'd like to know how to make work newline control character inside c-string returned by a sqlite3_column_text. For example, this is the text returned from a query to a sqlite database:

"Lorem ipsum\ndolor sit amet,\nconsectetur\nadipiscing elit."

and this is the code I use to get it:

char *body = (char *)sqlite3_column_text(statement, 1);
NSString *str = (body)?[NSString stringWithUTF8String:body]:@"";

The problem comes when I try to print it, because in return this:

Lorem ipsum\ndolor sit amet,\nconsectetur\nadipiscing elit.

instead of:

Lorem ipsum
dolor sit amet,
consectetur
adipiscing elit.

Using the same text but returned from a string column in Core Data works as intended, but I don't know why is doesn't in sqlite3. I hope someone can give directions because is quite frustrating.

score 2 · Accepted Answer · edited May 23 '17 at 10:00

Looking in the literal values section of the documentation of SQLite I found that the reason of why it return the two-character \n instead of newline is because C-style escapes using the backslash character are not supported because they are not standard SQL. So I tried a different aproach by using NSString instead of using SQLite directly:

char *body = (char *)sqlite3_column_text(statement, 1);
NSString *str = (body)?[NSString stringWithUTF8String:body]:@"";
NSString *container = [str stringByReplacingOccurrencesOfString:@"\\n"    
                                                     withString:@"\n"];

Thanks to this two posts, I get the idea of using \\n to replace \n correctly in the final NSString, so is possible to use other valid control characters.

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

The issue is that the value stored in the sqllite DB is the two-character \n and not the newline itself. You will have to either convert the \n into the newline (ASCII 10) character before adding the string to the DB or process the string after the SELECT query in your Objective-C code.

See the following answers for further info:

Trouble with newline character while programming for the iPhone

Preventing sqlite from escaping backslash

edited May 23 '17 at 11:55

Community

1
1

answered Jun 01 '11 at 00:32

Dave R.

7,206
3
30
52

You're very welcome, Raomon. Consider voting answers up if they help you, and accept the one which best answers your query. Good to see you got the issue solved to your satisfaction. – Dave R. Jun 03 '11 at 00:24

How to make work newline inside c-string returned by a sqlite3 text column?

2 Answers2