How to use the char(10) Function In SQL

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:

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:

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.