BigQuery String Functions 101: Syntax and Usage Simplified

Today, companies generate, store and manage huge volumes of data. Storing and querying such volumes of data can be costly and time-consuming, especially for an organization that doesn’t have the appropriate Infrastructure. To overcome this hurdle Google introduced BigQuery which is an enterprise Data Warehouse that leverages the processing power of Google’s Infrastructure to enable super-fast SQL queries. It allows you to move data from your database/desired source to Google BigQuery for optimized performance. In this article, we’re going to learn about BigQuery String Functions.

Strings are one of the most used data types in Google BigQuery. It is one of the most flexible data types. Therefore it becomes extremely important to know how to master Strings. Upon a complete walkthrough of this article, you will gain a decent understanding of Google BigQuery along with the salient features that it offers. You will also learn about different types of Google BigQuery String Functions along with their syntax and examples. Read along to learn more about BigQuery String Functions.

Prerequisites

• Basic Understanding of SQL.

What is Google BigQuery?

Google BigQuery is a popular Cloud-based enterprise Data Warehouse built for business acceleration. It gives users the ability to run complex SQL queries and perform an in-depth analysis of large datasets. Google BigQuery is built on Google’s Dremel technology for processing read-only data.
It leverages a Columnar Storage Model that supports data scanning at a tremendous speed, along with a Tree Structure that makes Querying and Aggregating results tremendously efficient. Google BigQuery is serverless, and it was designed to be highly scalable.

Google uses the existing Cloud architecture to successfully manage a serverless design. It also uses different data models that give users the ability to store dynamic data. In the later section of this article, you will learn about different types of BigQuery String Functions.

Key Features of Google BigQuery

Some of the key features of Google BigQuery are as follows:

• Scalability: To provide consumers with true Scalability and consistent Performance, Google BigQuery leverages Massively Parallel Processing and a Highly Scalable Secure Storage Engine. The entire Infrastructure with over a thousand machines is managed by a complex software stack.
• Storage: Google BigQuery uses a Columnar architecture to store datasets of mammoth scales. Column-based Storage has several advantages, including better Memory Utilization and the ability to scan data faster than typical Row-based Storage.
• Serverless: The Google BigQuery Serverless model automatically distributes processing across a large number of machines running in parallel, so any company using Google BigQuery can focus on extracting insights from data rather than configuring and maintaining the Infrastructure/Server. 
• Integrations: Google BigQuery as part of the Google Cloud Platform (GCP) supports seamless integration with all Google products and services. Google also offers a variety of Integrations with numerous third-party services, as well as the functionality to integrate with application APIs that are not directly supported by Google.

BigQuery String Functions: 5 Key Types 

Strings are an important part of any dataset, and manipulating and transforming them efficiently can make a huge impact on your analysis. There are many functions in Google BigQuery to manipulate and transform Strings. Some of the most important BigQuery String Functions along with their Syntax and Usage are discussed below:

1) Trimming Function

If you want to remove a specific character from your String then you can use the Trimming function to do so. Based on the position of the character that you wish to remove there are three kinds of BigQuery String Functions:

• TRIM (value1[, value2]): It removes all the leading and trailing characters that match value2. If no character is specified, whitespaces are removed by default.
• LTRIM (value1[, value2]): LTRIM Function removes the character specified from the left i.e it only removes the leading characters that match value2. Similar to the TRIM Function if a character is not specified, it removes the whitespaces by default.
• RTRIM (value1[, value2]): RTRIM Function removes the character specified from the right side i.e it only removes the trailing characters that match value2 (whitespace if no character is specified).

Examples of Trimming Functions are given below:

SELECT
  '  Original String_',
TRIM('  Original String_') AS trimmed,
LTRIM('  Original String_') AS left_trim,
RTRIM('  Original String_', "_") AS right_trim

TRIM Function will give Original String_ as output after removing all the whitespace. LTRIM Function will give Original String_ as output after removing white spaces from the left side and RTRIM Function will give Original String as output after removing value2 i.e ‘_’.

2) Concat Function

CONCAT Function is one of the most frequently used BigQuery String Functions that is used to combine two or more Strings. It receives the input Strings as arguments and returns the resultant concatenated String.

SYNTAX

SELECT
  CONCAT('A', " ", "B")

Following is an example of the concatenation of Strings using CONCAT Function:

SELECT CONCAT("Hello", " ", "World") as example;
 
+---------------------+
| example              |
+---------------------+
| Hello World            |
+---------------------+

3) String Comparison Function

When creating filters or CASE statements, it is often checked whether a String is similar to another Character String or a Substring. To do this, you can use one of the following BigQuery String Functions:

A) Starts With Function

The Starts With Function receives two strings as input and returns true if the first string starts with the second String.

SYNTAX

STARTS_WITH(value 1, value2)

Following is an example of Starts With Function: 

STARTS_WITH(‘Hello there’, ‘Hello’) as example 

The query written above will return true as the second string is a substring of the first string.

B) Ends With Function

The Ends With Function receives two Strings (values) as input and returns true if value1 ends with the substring value2.

SYNTAX

ENDS_WITH(value1, value2)

Following is an example of Ends With Function:

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)
 
SELECT
  ENDS_WITH(item, "e") as example
FROM items;
 
+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

C) Regexp Contains Function 

This function is used to check whether a particular pattern is present in a string or not. This function receives two arguments – Value and Pattern. It returns true if the pattern is present in the value.

SYNTAX

REGEXP_CONTAINS(value, regexp)

Following is an example of Regexp Function:

REGEXP_CONTAINS(Example, 'A') returns true.
if the Example contains 'ABCD'.

4) Character Length Function

This function is one of the most important BigQuery String Functions that is used to calculate the length of a Character String. The return type of the Character Length function is INT64.

SYNTAX

CHARACTER_LENGTH(value)

Given below is an example of Character Length Function:

WITH example AS
  (SELECT "abcde" AS characters)
 
SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
 
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| abcde      |                   5 |
+------------+---------------------+

5) Case Functions

There are certain BigQuery String Functions that are used to change the case of a String. They are as follows:

A) Lower Case Function

Lower Case Function receives a string as an argument and returns a lowercase string as the output.

SYNTAX

LOWER(value)

Following is an example of Lower Function:

SELECT LOWER(‘XYZD’) as lower

Which will return xyzd as output in lowercase.

B) Upper Case Function

This function receives a string as an input and returns the Uppercase String as the output.

SYNTAX

UPPER(value)

Following is an example of Upper Function:

SELECT UPPER(‘pqrs’) as example

Which will return PQRS as the output.

If you want to learn more about BigQuery String Functions, you can click here to check the official documentation where you can learn about all the types of BigQuery String Functions along with their syntax and examples.

6) Reverse Function

The REVERSE function in BigQuery is used to reverse the characters in a string.

SYNTAX

REVERSE(string)

Example of Reversing a Simple String:

SELECT REVERSE('Hello, World!') AS reversed_string;

In this example, the REVERSE function takes the string ‘Hello, World!’ and returns the result as !dlroW ,olleH.

7) Repeat Function

The REPEAT function in Google BigQuery is used to replicate a given string a certain number of times, creating a new string that consists of the original string repeated sequentially.

SYNTAX

REPEAT(string, number)

Example of Repeating a String:

SELECT REPEAT('BigQuery', 3) AS repeated_string;

In this example, the REPEAT function takes the string ‘BigQuery’ and repeats it three times, producing the result BigQueryBigQueryBigQuery.

8) Replace Function

The REPLACE function in Google BigQuery allows for the substitution of a specific substring within a string with another substring.

SYNTAX

REPLACE(original_string, old_substring, new_substring)

Example of Replacing Substrings:

SELECT REPLACE('Data Science', 'Science', 'Analysis') AS replaced_string;

In this example, the REPLACE function takes the string ‘Data Science’ and substitutes the substring ‘Science’ with ‘Analysis’, resulting in the output Data Analysis.

Conclusion

In this article, you learned about Google BigQuery and the salient features that it offers. You also learned about BigQuery String Functions and how you can use them to transform and manipulate strings in Google BigQuery. With your Data Warehouse, Google BigQuery live and running, you’ll need to extract data from multiple platforms to carry out your analysis. However, integrating and analyzing your data from a diverse set of data sources can be challenging and this is where Hevo Data comes into the picture.

Want to take Hevo for a spin?

Try Hevo’s 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about BigQuery String Functions. Let us know in the comments section below!

FAQs