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

Last Updated : 23 Oct, 2024

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.

Here, we will explore techniques such as using multiline strings and the CHR(10) function to insert line breaks effectively.

How to Insert a Line Break in 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:

Table of Content

Method 1: By Making the String Multiline
Method 2: Use CHR(10) Function

Setting Up the Environment

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:

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:

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.

Method 1: By Making the String Multiline

In Oracle PL/SQL, you can insert line breaks directly by pressing Enter within a string. This approach treats the string as a multiline text and inserts a newline character wherever you break the line.

Query:

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

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.

Method 2: Use CHR(10) Function

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:

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:

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:

The characters in Method 1's output are 'A'(97), ' '(32), newline(10) and 'B'(98). Here we see that the string of Method 1 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:

Explanation: So, we see that Method 1 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.

While both methods can produce similar results, there are subtle differences that may impact your choice:

Extra Spaces in Multiline Strings: When using multiline strings by pressing Enter, an extra space may be inserted before or after the newline character. This can be confirmed by examining the ASCII dump of the string.
Precise Control with CHR(10): The CHR(10) function allows for consistent and accurate insertion of newline characters without adding unintended spaces.

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. By understanding these techniques and their differences, you can effectively format text data in your Oracle database.