How to Insert a Line Break VARCHAR String in MySQL

When dealing with textual data in MySQL databases, maintaining proper formatting is important for readability and clarity. One common formatting requirement is to insert line breaks within VARCHAR and NVARCHAR strings. In this article, we will understand How to insert a line break in a MySQL VARCHAR/NVARCHAR string using two data types: VARCHAR (Variable Character) and NVARCHAR (National Variable Character).

Introduction to VARCHAR/NVARCHAR String in MySQL

In MySQL, VARCHAR (Variable Character) is a data type that allows us to store variable–length character strings. The length of a VARCHAR column is variable and can hold up to a specified maximum length. On the other hand, NVARCHAR (National Variable Character) is also a variable–length string data type, but it is used for character strings in the National Language Character Set.

Insert a line break in a MySQL VARCHAR/NVARCHAR string

Let’s see some examples of How to insert a line break in a MySQL VARCHAR/NVARCHAR string.

Example 1: Using the CONCAT Function

In this example, the CONCAT function is used to concatenate the desired text with a line break. The CHAR(13) and CHAR(10) represent the ASCII characters for carriage return and line feed, respectively. Together, they create a line break in the string.

Query:

-- Create a sample table
CREATE TABLE GeeksforGeeks 
(
    id INT PRIMARY KEY,
    text_data VARCHAR(255)
);

-- Insert a record with a line break
INSERT INTO GeeksforGeeks (id, text_data) 
VALUES (1, CONCAT('First line', CHAR(13), 
CHAR(10), 'Second line'));

-- Retrieve the data
SELECT * FROM GeeksforGeeks;

Output:

Explanation: Output of the code creates a table named GeeksforGeeks with columns id and text_data. It inserts a record with id = 1 and text_data containing two lines separated by a line break. The output will show the inserted record with a line break between “First line” and “Second line.

Example 2: Using the CONCAT_WS Function

In this example, the CONCAT_WS (Concatenate With Separator) function is used to concatenate the specified strings with an empty separator. This effectively combines the strings into one, creating a line break between them.

Query:

-- Create a sample table
CREATE TABLE GeeksforGeeks(
    id INT PRIMARY KEY,
    text_data VARCHAR(255)
);

-- Insert a record with a line break
INSERT INTO GeeksforGeeks (id, text_data) 
VALUES (1, CONCAT_WS('\n', 'First line', 'Second line'));

-- Retrieve the data
SELECT * FROM GeeksforGeeks;

Output :

Explanation: Output of the code creates a table named GeeksforGeeks with columns id and text_data. It inserts a record with id = 1 and text_data containing two lines separated by a line break. The output will show the inserted record with a line break between “First line” and “Second line.

Conclusion

Effectively inserting line breaks in MySQL VARCHAR/NVARCHAR strings is important for maintaining data clarity. By using functions like CONCAT and CONCAT_WS, you can easily concatenate text and special characters resulting in a well-formatted and readable format. The choice between these methods depends on the specific formatting needs of our data.