STD_SPLIT_BQ_UDF.sql

/**
 * This is a UDF of BigQuery legacy SPLIT statement.
 *
 * Parameters:
 *   col - column that you want to split
 *   del - delimiter to split by
 */
CREATE TEMPORARY FUNCTION STD_SPLIT(col STRING, del STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """  
    return col.split(del);
""";


/* Example of usage */

SELECT str, STD_SPLIT(str, ".") as splits

FROM (SELECT "xxx.zzz" str UNION ALL 
      SELECT "aa.cc.b.s"   UNION ALL
      SELECT "aaaaaaa"     UNION ALL 
      SELECT "x...x")

CREATE TEMPORARY FUNCTION STD_MAX(col1 TIMESTAMP, col2 TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE js AS """  
    if(col1 >= col2) return col1;
    return col2;
""";

WITH t AS (
  SELECT 
    TIMESTAMP('2018-01-01 00:00:00') a, 
    TIMESTAMP('2018-01-02 00:00:00') b
)

SELECT
  STD_MAX(a, b) as max    
FROM
  t

-- returns min col between two cols or NULL if both are NULL
CREATE TEMP FUNCTION MIN_TIMESTAMP(a TIMESTAMP, b TIMESTAMP) AS (
  CASE
    WHEN a <= b THEN a 
    WHEN a >  b THEN b
    WHEN a IS NOT NULL AND b IS NULL THEN a
    WHEN b IS NOT NULL AND a IS NULL THEN b
  END
);

-- returns max col between two cols or NULL if both are NULL
CREATE TEMP FUNCTION MAX_TIMESTAMP(a TIMESTAMP, b TIMESTAMP) AS (
  CASE
    WHEN a >= b THEN a 
    WHEN a <  b THEN b
    WHEN a IS NOT NULL AND b IS NULL THEN a
    WHEN b IS NOT NULL AND a IS NULL THEN b
  END
);

WITH 

t AS (
  SELECT TIMESTAMP '2018-01-01 00:00:01' a, TIMESTAMP '2018-01-01 00:00:02' b UNION ALL
  SELECT TIMESTAMP '2018-01-01 00:00:02' a, TIMESTAMP '2018-01-01 00:00:01' b UNION ALL
  SELECT TIMESTAMP(NULL)                 a, TIMESTAMP(NULL)                 b UNION ALL
  SELECT TIMESTAMP '2018-01-01 00:00:01' a, NULL                            b UNION ALL
  SELECT NULL                            a, TIMESTAMP '2018-01-01 00:00:01'  b
)

SELECT 
  a,
  b,
  MIN_TIMESTAMP(a, b) as min_ts ,
  MAX_TIMESTAMP(a, b) as max_ts
FROM t