Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'TRIM' or 'PROPER' in BigQuery

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?

like image 645
Ilja Avatar asked Apr 26 '15 07:04

Ilja


2 Answers

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

like image 165
Mosha Pasumansky Avatar answered Oct 13 '22 07:10

Mosha Pasumansky


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.

like image 32
Abhishek R Avatar answered Oct 13 '22 08:10

Abhishek R