Cleaning up STRINGS in BigQuery

Data is collected and processed in a number of ways, and it should come as no surprise that it's not always perfect.

Perhaps the most important thing you need to do before analyzing data is have a look at how it's presented and check for irregularities.

Before any sound analysis a great deal of attention needs to be paid to cleaning the data.

Take string columns for instance. In BigQuery, as with other engines, there is a wealth of functions helping you to process strings, including:

- TRIM/RTRIM/LTRIM for getting rid of the whitespace
- REPLACE to replace a substring with another one
- UPPER/LOWER/NORMALIZE etc to control casing
- SUBSTR/SUBSTRING to cut strings and so on.

The main goal here is to bring everything to a common denominator, being able to tell which observations belong together and which data can be considered "missing".

SELECT '' AS city --empty string
UNION ALL
SELECT ' ' AS city --whitespace
UNION ALL
SELECT 'New York' AS city
UNION ALL
SELECT 'Athens' AS city
UNION ALL
SELECT ' New York' AS city --whitespace before
UNION ALL
SELECT 'New York ' AS city -- whitespace after
UNION ALL
SELECT NULL AS city       -- NULL value

SELECT DISTINCT city FROM input_data

SELECT DISTINCT NULLIF(TRIM(city),'') AS city FROM input_data