Wrapping

We wish to format a string as a paragraph with a certain line width. In other words, we wish to insert a newline character at a given line width (in number of characters). This is often useful for display purposes e.g. in notebooks or web apps.

SELECT col_1,
       REGEXP_REPLACE(col_1, r'(.{1,10})( +|$\n?)', '\\1\\2\n')  as col_2
FROM table_1;

Here is how this works:

• REGEXP_REPLACE(col_1, r'(.{1,10})( +|$\n?)', '\\1\\2\n'): This function performs a regular expression (regex) search and replace on the col_1 column. The function takes three arguments:
  • col_1: The column on which the regex search and replace is performed.
  • r'(.{1,10})( +|$\n?)': This is the regular expression pattern to search for. The pattern .{1,10} matches any character (.) that occurs 1 to 10 times ({1,10}). The second part of the pattern, ( +|$\n?), is a non-capturing group ((?:)) that matches either one or more spaces ( +) or the end of a line ($) followed by an optional newline character (\n?).
  • \\1\\2\n: This is the string that will be used to replace the matches found by the regular expression. The \\1 and \\2 are backreferences to the first and second captured groups in the regular expression pattern. The \n is a newline character.
• Therefore, the REGEXP_REPLACE will wrap the values in the column col_1 into a paragraph where each line has a maximum width of 10 characters.