15 Feb 21 ·  Cpaul in Guides ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds