Скачать или смотреть How to Trim Spaces After Splitting Strings in BigQuery

Learn how to effectively use the `TRIM` function in BigQuery to remove extra spaces after splitting strings in your dataset.
---
This video is based on the question https://stackoverflow.com/q/68940887/ asked by the user 'Pierre-Alexandre' ( https://stackoverflow.com/u/14897019/ ) and on the answer https://stackoverflow.com/a/68942199/ provided by the user 'rtenha' ( https://stackoverflow.com/u/11643858/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Trim after a split of a string on BigQuery

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Trimming Strings in BigQuery: A Guide to Removing Extra Spaces

When working with data in BigQuery, it's common to encounter strings that have additional spaces, especially when they're created through operations like splitting. If you've found yourself dealing with variables that have unwanted spaces—like names or subjects after a split—you are not alone. In this guide, we will explore how to effectively trim these extra spaces from the results of your SQL queries.

Understanding the Problem

Let’s say you have a dataset consisting of subjects or student names represented as strings, such as:

english, chinese, french

math, physics

taiwanese, russian, italian, turkish

Each string may contain extra spaces that can cause confusion or inaccuracies in your analysis. For instance, the string may appear as chinese or math when you split them, resulting in unwanted leading or trailing spaces.

In a recent scenario, a user needed to apply the TRIM function to their SYMB variable after splitting the strings from two columns: AZE (languages) and EPI (subjects). The challenge was to ensure that the trimming occurred after the split operation to provide clean results.

The Solution: Using Common Table Expressions (CTEs)

To effectively trim whitespace from your split strings in BigQuery, you can utilize Common Table Expressions (CTEs) to manage the process in stages. Below, we will break down the solution step-by-step.

Step 1: Define Your Base Data

Create a CTE that holds your base data. You can mimic the user's data input by defining the students as follows:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Split and Concatenate

Next, create another CTE that splits the AZE and EPI into an array and concatenates them into a single column, SYMB:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Trim the Split Items

In the next step, you will use the UNNEST function to expand the SYMB array. Here, you will also apply the TRIM function to clean up the results:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Select the Final Results

Finally, select the cleaned up columns:

[[See Video to Reveal this Text or Code Snippet]]

Full SQL Query

To summarize the entire process, the complete SQL query will look like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In this post, we have explored how to effectively use the TRIM function in BigQuery to clean up your data after performing a split operation. By systematically breaking down your SQL query with CTEs, you can ensure that your data analysis is both accurate and efficient. Remember always to look for ways to clean your data—it's a crucial step in any data analysis workflow!

Now, you can apply these techniques in your own BigQuery projects and say goodbye to unwanted extra spaces in your datasets!