Replace function in BigQuery

The replace function is replace all occurrence of search string in the source string with the replace string. We can use this function either to replace or remove portions of a string.

Syntax

• original_value – It is a source string in which we do the replacement.
• from_value – It is a search string that we want to replace.
• to_value – It is a replacement string for the from_value.

Example 1 : Replace string in BigQuery

Consider that we have a table called as customer_360 in BigQuery. It contains the following columns cust_id, category_code, subscribed_products, address and country. The table has below records.

The column category_code is a String data type which contains a value ‘PR‘. Our goal is to replace ‘PR‘ with the new string ‘TECH‘. Let’s write a query for that using Replace function.

In the SELECT query, we have included both original category code and new category code. As shown below, the REPLACE function has changed the string from ‘PR’ to ‘TECH’ in category code. The remaining values in the column is not changed by the function.

Example 2 : Replace character in string

The table customer_360 has another column as subscribed_products. In that column, the product details are split by character hash(#). Let’s replace all hash(#) character with hypen(–)

As shown below, the delimiter is changed from # to – in the subscribed products. Similarly we can use other characters such as comma(,) or pipe(|) in the REPLACE function.

Example 3 : Update and Replace part of a string

For this example, we will take a column address from the same table customer_360. If we look at the values in that column, we can see a string ‘St’. It is an abbreviation for Street.

Our goal is to replace the string ‘St’ with new string ‘Street’ in address column. First we will write a select query using Replace function. Once we verified the results, we can write a UPDATE query with REPLACE function.

As we shown below, the string Street is already present in one row. Because of that the replace function is changed the value to Streetreet for that customer. Apart from this, the remaining rows are changed correctly. Let’s fix this by writing the WHERE condition in query.

Replace function with CONTAINS_SUBSTR

The string St is always ends with comma(,) in address column. So we can use this as a condition in the Select query. For that, we are going to use another function CONTAINS_SUBSTR which returns TRUE if the given string is exist in the column.

As per the where condition, the query fetched only the rows which has string ‘St,‘ in address column. Then it applied the REPLACE function to change the string.

Let’s perform UPDATE with this REPLACE statement on the table customer_360.

As shown below, we have updated the address column using REPLACE function. It replaced the given string in 3 rows.

Let’s verify the results using SELECT query. As shown below, we replaced the string from ‘St’ to ‘Street’ in the column address.

Recommended Articles

• Regexp_replace function in BigQuery with examples
• Regexp_extract function in BigQuery with examples
• Substring function in BigQuery with examples
• How to flatten an array using UNNEST function in BigQuery?