How to declare variable inside BigQuery UDF body?

Question

I am trying to create a UDF function with a while-loop on BigQuery, but I am not seeing any syntactic guidelines in the documentation, which addresses this case specifically, nor which addresses the declaration of variables in side the UDF body.

Context: I'm trying to build a function to apply title case to a string.

I tried:

CREATE CREATE OR REPLACE FUNCTION mydataset.title_case(word STRING) as (
    DECLARE i INT64;
    SET i = ARRAY_LENGTH(SPLIT(word, " "));
    ...
);

However it doesn't like the DECLARE or SET in the UDF body. What's the right syntax for this?

Answer 1

Regarding your question about how to use DECLARE and SET within an UDF, you have to declare and set the variable in the beginning of your code. Then, you pass it as an argument to your UDF, the syntax would be;

DECLARE x ARRAY <String>; 
SET x = (SELECT ARRAY_LENGTH(SPLIT(word, " ")) FROM `project_id.dataset.table`);

CREATE CREATE OR REPLACE FUNCTION mydataset.title_case(word STRING, x INT64) as (
#your function...
);

Notice that the variable is set according to a value from a table, using SELECT. Also, you it is passed as an argument to the UDF.

In addition, I was able to create a JavaScript UDF to apply title case to a string without SET and DECLARE. I have only used JS's builtin methods. You can use it as follows:

CREATE TEMP FUNCTION title_case(str String)
RETURNS string
LANGUAGE js AS """
  str = str.split(' ');
  for(var i = 0; i < str.length; i++){
    str[i] = str[i].charAt(0).toUpperCase() + str[i].slice(1); 
  }
  return str.join(' ');
""";

WITH data AS (
SELECT "jack sparrow" AS name
)

SELECT title_case(name) as new_name FROM data

and the output,

Row new_name    
1   Jack Sparrow