How to insert a line break in a String PL/SQL

In PL/SQL, inserting line breaks into VARCHAR or NVARCHAR strings can be a good way to enhance the readability and presentation of our data. Whether we are formatting output for display or preparing text for storage, knowing how to insert line breaks is a helpful skill. In this guide, we’ll explore the various techniques and examples for inserting line breaks in PL/SQL VARCHAR/NVARCHAR strings.

How to Insert a Line Break in a PL/SQL?

Inserting a line break in a PL/SQL statement can make our output more readable and organized. Below is the method which is used to insert a line break in a PL/SQL VARCHAR/NVARCHAR string as given below:

1. By Making the string Multiline
2. Use CHR(10)

let’s Setting Up an Environment for Insert a Line Break in a PL/SQL

Let us create a table and insert string values into it. We will create a zoo table and keep some animals in it.

Query:

CREATE TABLE an 
(
    ID INT,
    NAME VARCHAR2(20),
    TYPE VARCHAR2(20),
    ABOUT VARCHAR2(100)
);

INSERT ALL
    INTO zoo VALUES (1, 'Tif', 'Frog', 'A green one.')
    INTO zoo VALUES (2, 'Bim', 'Sparrow', 'Blue Eyes.')
    INTO zoo VALUES (3, 'Kiz', 'Snake', 'Sizzling.')
SELECT * FROM DUAL;

SELECT * FROM zoo;

Output:

Output

Explanation: We have inserted the strings into the table.

Note: Don’t worry about DUAL it is just a syntax requirement. DUAL is a dummy table with one row in it. It is used in cases where we want to just get the output of some operation like SELECT 3+4. we can run this statement, but in PL/SQL we need to select it out of a table like SELECT 3+4 FROM DUAL. The INSERT ALL command also requires a select statement, therefore dual is put there.

Let’s try to insert a new record having a newline character.

Query:

INSERT INTO zoo VALUES (4, 'Fie', 'Cow', 'A white one. \n Has black patches.');
SELECT * FROM zoo;

Output:

Output

Explanation: The newline didn’t get inserted as a special character but as two separate normal characters. We will now look how to insert newline character into a string.

1. By Making the String Multiline

We can insert newline character directly into the string by pressing enter. The string which is broken down into two lines works fine and works as a string with newline character. See the following example.

Query:

INSERT INTO zoo VALUES (5, 'Kol', 'Peacock', 'A blue one.
With beautiful feathers.');
SELECT * FROM zoo;

Output:

Output

Explanation: As we can see the multiline string is allowed in PL/SQL. We can insert as many lines as we want without any problems.

2. Use CHR(10)

We can insert newline into string using its ASCII value (i.e. 10). we can read about the CHR function here. Basically it forms the character from the given number. Let’s insert newline into our string using this. Here we will use the concatenation operator `||` to concatenate strings. See the following example.

Query:

INSERT INTO zoo VALUES (6, 'Wer', 'Zebra', 'A tall one.' || CHR(10) || 'With big black stripes.');
SELECT * FROM zoo;

Output:

Output

Explanation: As we can see the newline character has been inserted into the string.

Choosing CHR(10) Over String Multiline

Although the output of the Method 2 and 1 looks like the same, there is a slight difference in the results. Before understanding this let’s verify the claim. See the following example.

Query:

SELECT CASE
    WHEN 'a
b' = 'a' || CHR(10) || 'b' THEN 'Equal'
    ELSE 'Not Equal'
END AS Compare
FROM DUAL;

Output:

Output

Explanation: Here we compare the two strings using the case statement. The result, as the output says is that the statements are not equal.

To understand this, let’s see the DUMP of the strings. DUMP is a function that prints the string however it prints each byte’s ASCII code. Now let’s see the dump of the two strings.

Query:

SELECT 'Method1' AS Method, DUMP('a
b') FROM DUAL
UNION
SELECT 'Method2', DUMP('a' || CHR(10) || 'b') FROM DUAL;

Output:

Output

The characters in Method1‘s output are ‘A'(97), ‘ ‘(32), newline(10) and ‘B'(98). Here we see that the string of Method1 has an extra space character in it. To prove it let’s remove the newline character from the Method1’s string and the print it.

Query:

SELECT REPLACE('a
b', CHR(10)) FROM DUAL;

Output:

Output

Explanation: So, we see that Method1 inserts an extra space character into the string. If you don’t have any problem with this then go on with any method. Otherwise, go for Method2 as it only inserts a newline.

Conclusion

Overall in PLSQL we have learnt about how to insert a newline character into table. We cannot use the famous `\n` character to put newlines into strings in PLSQL. Instead, we need to use other methods to insert newlines into strings. The first is to use multiline strings and simple press enter to insert newline. The second being the use of CHR function and the concatenation operator to inject the character wherever we want. Although these two methods put similar output but there is a subtle difference in the results, the first method puts an extra space character before each newline.