4

I am using UDF to do a little regex for phrase like 'test/test' and I came across an error that I can't correct.

CREATE TEMPORARY FUNCTION parseMethod(queryString STRING)
RETURNS STRING
LANGUAGE js AS
\"\"\"
var match_regex = /test\/(\w+)/i;
var found_method;
if(found_method = queryString.match(match_regex)){
  method_list = found_method[1];
}
return method_list;
\"\"\";

SELECT
parseMethod('test/test') AS result

When I run this code, I get an error saying

Error in query string: Error processing job
'click-1315:XXXXXXXX': Syntax error:
Illegal escape sequence: \/

The javascript function works fine in node.js so I am guessing parsing of BigQuery doesn't like this format. I also tried \\/ but same error. How can I fix this?

dorachan2010
  • 981
  • 3
  • 12
  • 21

1 Answers1

3

You just need to remove the backslashes in the CREATE TEMPORARY FUNCTION statement and add a couple in the regex. This seems to work:

CREATE TEMPORARY FUNCTION parseMethod(queryString STRING)
RETURNS STRING
LANGUAGE js AS
"""
var match_regex = /test\\/(\\w+)/i;
var found_method;
if(found_method = queryString.match(match_regex)){
  method_list = found_method[1];
}
return method_list;
""";

SELECT
parseMethod('test/test') AS result;
Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • I had those `\"\"\"` because I was using the CLI in a shell script. Are you just using the bigquery api in some language? – dorachan2010 Jan 12 '17 at 19:14
  • 1
    I tried it through the UI. You can put that query text in a file, though, and then pipe it into the CLI. See http://stackoverflow.com/a/41571272/6253347 for an example. – Elliott Brossard Jan 12 '17 at 19:21