New Line character \n in SQLite concatenate

Question

I know that, in some Programming language, if you do something like:

'This is on first line \n This is on second line'

Then it will display properly like this:

This is on first line
This is on second line

When I concatenate a string in a SQLite database

SELECT *, [FIELD1] || '\n'  || [FIELD2] from TABLE

(where [FIELD1] = This is on first line [FIELD2] = This is on second line)

it displays as such:

This is on first line \n This is on second line

Is there a reason that it isn't displaying the \n characters properly?

score 49 · Answer 1 · answered May 29 '14 at 10:34

49

SQL has no backslash escapes.

You can generate a newline in a string by writing it directly in the query:

SELECT [Field1] || '
' || [Field2] FROM MyTable

SELECT [Field1] || x'0a' || [Field2] FROM MyTable

SELECT [Field1] || char(10) || [Field2] FROM MyTable

answered May 29 '14 at 10:34

CL.

2

`char()` works only in fairly recent SQLite, which may not be present if you're on a really old long term supported distro like CentOS. – Damian Yerrick Jul 19 '16 at 16:29
@DamianYerrick indeed. Changelog for [2013-03-18 (3.7.16)](https://sqlite.org/changes.html#version_3_7_16) says "Added SQL functions unicode(A) and char(X1,...,XN)". "Enterprise Linux" 6 comes with sqlite version 3.6.20. – Cristian Ciupitu Nov 11 '20 at 13:54

score 3 · Answer 2 · edited Dec 01 '15 at 11:17

3

Try char(13) (if you want to see it in Windows notepad).

select col || char(13) from mytable

edited Dec 01 '15 at 11:17

ceving

answered Dec 23 '14 at 19:49

live-love

2 Answers2