How to Insert a Line Break in a SQLite VARCHAR String

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.

How to Insert Line Break in SQLite String?

Sometimes when we working with SQLite, inserting a line break in a VARCHAR/NVARCHAR string can be tricky. So below are the approaches that help us to insert a new line character into a string are as follow:

1. Using the Escaped Newline Character
2. Using the char(10) Function
3. Using the X’0A’ Hexadecimal Escape Sequence

1. Using the Escaped Newline Character

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:

Using the Escaped Newline Character output

2. Using the char(10) Function

SQLite’s char function can be used with the ASCII code 10 to represent a newline character.

Syntax:

INSERT INTO YourTable (YourColumn) VALUES ('First line' || char(10) || 'Second line');

Example: Here’s an example of creating a table named Student in SQLite

-- Create the table
CREATE TABLE Student (
    ID INTEGER PRIMARY KEY,
    Name VARCHAR(50),
    Description NVARCHAR(255)
);

-- Insert values
INSERT INTO Student (Name, Description) VALUES ('Alice', 'Math class' || char(10) || 'Homework due on Friday');
INSERT INTO Student (Name, Description) VALUES ('Bob', 'English class' || char(10) || 'Book report submission');
INSERT INTO Student (Name, Description) VALUES ('Charlie', 'History class' || char(10) || 'Project presentation');
INSERT INTO Student (Name, Description) VALUES ('David', 'Science class' || char(10) || 'Lab experiment');
INSERT INTO Student (Name, Description) VALUES ('Eva', 'Computer Science class' || char(10) || 'Coding assignment');

-- Query to retrieve and display data in tabular format
.mode column
.headers on
SELECT * FROM Student;

Output:

Using the char(10) Function output

3. Using the X’0A’ Hexadecimal Escape Sequence

Another approach is to use the hexadecimal escape sequence X’0A’ to represent a newline character.

Syntax:

INSERT INTO YourTable (YourColumn) VALUES ('First line' || X'0A' || 'Second line');

Example: Let’s create an Employee table with a Description column of type VARCHAR

-- Create the Employee table
CREATE TABLE Employee (
    ID INTEGER PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(255)
);

-- Insert values with line breaks
INSERT INTO Employee (Name, Description) VALUES 
    ('John Doe', 'Worked on project X' || X'0A' || 'Met with clients'),
    ('Jane Smith', 'Attended team meeting' || X'0A' || 'Reviewed project documentation'),
    ('Bob Johnson', 'Developed new feature' || X'0A' || 'Fixed bugs'),
    ('Alice Williams', 'Conducted training session' || X'0A' || 'Prepared progress report'),
    ('Charlie Brown', 'Participated in brainstorming session' || X'0A' || 'Collaborated with team');

-- Select to verify the inserted data
SELECT * FROM Employee;

Output:

Using the X’0A’ Hexadecimal Escape Sequence output

Conclusion

Overall, inserting a line break in a SQLite VARCHAR/NVARCHAR string can be achieved using various methods, such as the escaped newline character, the char(10) function, or the X’0A’ hexadecimal escape sequence. These approaches allow for the effective formatting of data in SQLite, enhancing its usability and readability. By understanding and implementing these methods, developers can efficiently manage text data in SQLite databases, improving the overall user experience.