How to use the Escaped Newline Character In SQL

We can use the escaped newline character \n directly in your string to represent a line break.

Syntax:

INSERT INTO YourTable (YourColumn) VALUES ('First line\nSecond line');

Example: Let’s say we have a table named Employee with a column Address of type VARCHAR/NVARCHAR. Here’s we are inserting values with a line break using the escaped newline character:

-- Create the table
CREATE TABLE Employee (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT,
    Address VARCHAR(255)
);

-- Insert additional values
INSERT INTO Employee (Name, Address) VALUES 
     ('John Doe', '123 Main Street\nApt 45'),
    ('Jane Smith', '456 Oak Avenue\nSuite 102'),
    ('Alice Johnson', '789 Pine Street\nFloor 3'),
    ('Bob Anderson', '101 Elm Avenue\nSuite 205'),
    ('Eva Martinez', '555 Oak Lane\nApt 15');

-- Display the contents of the Employee table
SELECT * FROM Employee;

Output:

SQLite is a popular relational database management system known for its simplicity and flexibility. However, inserting a line break in a VARCHAR/NVARCHAR string can be challenging due to SQLite’s limited support for special characters.

In this article, we will explore different approaches to inserting a line break in an SQLite VARCHAR/NVARCHAR string with the help of various examples which help us to format our data effectively.