Trimming

We wish to remove leading and trailing whitespaces or a set of characters from a string. We will also cover how to eliminate duplicate spaces within the string.

Surrounding

We wish to remove leading and trailing white space characters.

SELECT col_1,
       TRIM(col_1) AS col_2,
FROM table_1;

Here is how this works:

  • We use the function TRIM() to eliminate leading and trailing white spaces.
  • The output table will have the same number of rows as the input table table_1 with an added column col_2 where each value is the corresponding value of col_1 with any leading or trailing white space characters removed.

Extension: Specify Characters to Strip

We wish to remove leading and trailing characters from a string.

In this example, we wish to trim leading and trailing * from col_1.

SELECT col_1,
       TRIM(col_1, '#*') AS col_2
FROM table_1;

Here is how this works:

  • By default, TRIM() will remove white space characters on both sides of the string.
  • We can pass a set of charterers to remove from the string, in this example '#*'. TRIM() will remove any '*' or '#' if they are leading or trailing.
  • TRIM() will first remove the first leading and trailing character of the string, and then will keep removing until it finds a leading or trailing character that is not part of the set. Check Code I/O to see examples in action.

Extension: Trim One Side Only

SELECT col_1,
       LTRIM(col_1) AS col_2,
       RTRIM(col_1) AS col_3,
FROM table_1;

Here is how this works:

  • We use LTRIM() to remove all leading characters, in this case, whitespace, and RTRIM() to remove all trailing characters.

Intermittent

We wish to replace duplicate white space characters within the string with a single white space.

SELECT col_1,
       REGEXP_REPLACE(col_1, '\\s{2,}', ' ') AS col_2
FROM table_1;

Here is how this works:

  • We can use REGEXP_REPLACE() to capture all occurrences of intermittent duplicate white spaces and replace them with a single white space.
  • We capture occurrences of intermittent duplicate white spaces via the regular expression "\\s{2,}" where:
    • \\s denotes a white space character and
    • {2, } specifies that we wish to capture a pattern or two or more occurrences.
SQL
I/O