BigQuery using Regex in Javascript UDF escaping dynamic value

Question

Im trying to use regex with dynamic variable in BigQuety with JS UDF but there is some problem with the escaping and I don't know how to solve it.

CREATE TEMP FUNCTION `replaceRegex`(org STRING, to_replace STRING, replace_to STRING)
RETURNS STRING LANGUAGE js AS
"""
    re_string = `\b${to_replace}\b`
    let re = new RegExp(re_string, 'gi');
    return org.replace(re, replace_to);
""";

SELECT replaceRegex('The quick brown fox jumps over the lazy dog', 'fox', 'bear')

This version does not work.

But I change: \b${to_replace}\b to:

re_string = /\bfox\b/

Then it does work.

But I cant hardcode my regex it has to be dynamic value passed.

user_14733669 · Answer

Adding r at the start of your triple quoted string makes your code work as it treats everything as raw strings see this reference. Your code should be:

CREATE TEMP FUNCTION `replaceRegex`(org STRING, to_replace STRING, replace_to STRING)
RETURNS STRING LANGUAGE js AS
r"""
    re_string = `\b${to_replace}\b`
    let re = new RegExp(re_string, 'gi');
    return org.replace(re, replace_to);
""";

SELECT replaceRegex('The quick brown fox jumps over the lazy dog', 'fox', 'bear')

Test:

BigQuery using Regex in Javascript UDF escaping dynamic value

1 Answers