BigQuery UDF: A match made in heaven!

BigQuery supports User Defined Functions written in SQL or Javascript.

This is such a nice feature since you are effectively freed from SQL limitations. Or if a built in function doesn’t exist or isn’t serving your purposes, you can simply create your own using Javascript and use it inside of your SQL queries or views!

(Bear in mind, you won’t be able to use a temporary function and save it inside of a view. Instead, you can simply create it as a persistent function and then reference it inside of your views!)

Also, while sometimes you can achieve an operation in SQL, it is often so much more performant and elegant in Javascript!

Let’s look at an example of performing some Camel Case text formatting:

CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
function toCamelCase(phrase) {
    var out = “”;
    try {
        phrase.split(” “).forEach(function (elidx) {
            var add = el.toLowerCase();
            out += (add[0].toUpperCase() + add.slice(1) + ” “);
        return out;
    catch {
        return phrase;

Leave a Comment