'TRIM' or 'PROPER' in BigQuery

Question

is there a way to normalize strings in BigQuery?

My dataset looks like:

Alfa Beta

Alfa BETA

alfa beta //with a space after 'beta'

By now I can use lower or upper to normalize the letters but I don't know how to eliminate spaces before and after the text. Does BigQuery have a function like 'TRIM' in Excel?

Solution 1

BigQuery does have LTRIM (trims spaces from left) and RTRIM (trims spaces from right) functions. (Strings functions documentation at https://cloud.google.com/bigquery/query-reference#stringfunctions missed them, we will fix this shortly).

Solution 2

Yes. BigQuery has support for TRIM to remove both leading and trailing spaces.

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#trim

You can also use LTRIM for spaces on the left and RTRIM for spaces on the right.

Comments

[zipline86]

Does that mean that I have to use both like this RTRIM(LTRIM(column_here)) to get rid of white space on both sides?