String REPLACE function in BigQuery with examples

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.

BigQuery replace function examples
BigQuery replace function examples

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.

Try to Execute

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.

Replace string example in BigQuery
Replace string example in BigQuery

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

Try to Execute

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.

replace character in string BigQuery
replace character in string BigQuery

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.

Try to Execute

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 the st with Street in BigQuery
Replace the st with Street in BigQuery

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.

Try to Execute

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.

contains_substr example in BigQuery
contains_substr example in BigQuery

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.

Update with replace function in BigQuery
Update with replace function in BigQuery

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

query results after UPDATE statement in BigQuery
query results after UPDATE statement in BigQuery

Recommended Articles