TRANSLATE function equivalent UDF in Google BigQuery

Translate function helps you to replace characters from a string. Translate function does not exist in BigQuery. However, you can achieve the functionality by creating an UDF (User defined function)

CREATE FUNCTION DB.TRANSLATE(expression STRING, characters_to_replace STRING, 
characters_to_substitute STRING) AS (
IF(LENGTH(characters_to_replace) < LENGTH(characters_to_substitute) OR
LENGTH(expression) < LENGTH(characters_to_replace),
expression,
(
SELECT
	STRING_AGG(
	IFNULL(
	(SELECT ARRAY_CONCAT([c],
	SPLIT(characters_to_substitute,''))[SAFE_OFFSET((SELECT IFNULL(MIN(o2) + 1,
	0) FROM
	UNNEST(SPLIT(characters_to_replace,29'')) AS k WITH OFFSET o2
	WHERE k = c))]
	), ''), '' ORDER BY o1)
	FROM UNNEST(SPLIT(expression, ''))
	AS c WITH OFFSET o1
))
);

Testing the UDF:

DB.TRANSLATE('1tech23', '123', '456')
 Result: '4tech56'

Cpaul

posted on 15 Feb 21