Alignment

We wish to align text to the left, right, or center within a given width specified as a number of characters.

Note that if the length of the string is larger than the specified line width, we return the original string as is.

Left Align

We wish to align text to the left of each line with empty spaces added to the right to fill the desired width of the line.

In this example, we wish to align the values of the string column col_1 to the left while setting the line width to 25.

SELECT col_1,
       IF(LENGTH(col_1) >= 25, col_1, RPAD(col_1, 25)) AS col_2
FROM table_1;

Here is how this works:

  • SQL doesn't offer an explicit align function, therefore we realize alignment via padding with the RPAD() function. See Padding.
  • We use the RPAD() for alignment as follows:
    • We pass the column whose values we wish to align as the first argument; which here is col_1.
    • We pass the line width, which here is 25, as the second argument.
    • We do not pass any value to the pattern parameter because the default is an empty space like we need.

Right Align

We wish to align text to the right of each line with empty spaces added to the left to fill the desired width of the line.

In this example, we wish to align the values of the string column col_1 to the right while setting the line width to 25.

SELECT col_1,
       IF(LENGTH(col_1) >= 25, col_1, LPAD(col_1, 25)) AS col_2
FROM table_1;

Here is how this works:

This works similarly to the Left Align case above except that we add padding to the left, hence LPAD(), because we wish to align right (in right aligning, the empty space is on the left).

Center Align

We wish to align text to the center of each line with empty spaces added to the left and the right to fill the desired width of the line.

In this example, we wish to align the values of the string column col_1 to the center while setting the line width to 25.

WITH table_2 AS
         (SELECT col_1,
                 CASE
                     WHEN LENGTH(col_1) >= 25 THEN col_1
                     ELSE LPAD(col_1,
                               LENGTH(col_1) +
                               CAST(FLOOR(((25 - LENGTH(col_1)) / 2)) AS int64)) END AS col_1_l
          FROM table_1)

SELECT col_1,
       IF(LENGTH(col_1_l) >= 25, col_1_l
           , RPAD(col_1_l, 25)) col_2
FROM table_2;
  • We do this operation over two steps for readability.

  • In table_2 CTE we carry our left padding to create a new column col_1_l as follows:

    • The CASE expression has two branches:
      • WHEN LENGTH(col_1) >= 25 THEN col_1: If the length of the col_1 value is already at least 25, the value is returned as is.
      • ELSE LPAD(col_1, LENGTH(col_1) + CAST(FLOOR(((25 - LENGTH(col_1)) / 2)) AS int64)): If the length of the col_1 value is less than 25, it is padded with spaces on the left side until it has a length of 25.
      • The number of spaces to add is calculated by subtracting the length of col_1 from 25 and then dividing the result by 2.
      • The FLOOR function is used to round down to the nearest integer. The result of the division is then cast to the int64 type using the CAST function.
      • Finally, the LPAD function is used to pad col_1 on the left side with the calculated number of spaces.

  • We then use an IF function. If the length of the value in col_1_l is already at least 25, it returns the value as is. If the length is less than 25, it pads the value with spaces on the right side until it has a length of 25 and stores the result in a new column called col_2.

SQL
I/O