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.
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.