0
264
ViewsBigQuery using Regex in Javascript UDF escaping dynamic value
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.
·
Santiago Trujillo
1 answers
Answer question0
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:
·
Santiago Trujillo
Report
Answer question