teradata

Teradata UDFs

This directory contains user-defined functions which mimic the behavior of proprietary functions in Teradata. Each UDF within this directory will be automatically synchronized to the bqutil project within the td dataset for reference in queries.

For example, if you'd like to reference the nullifzero function within your query, you can reference it like the following:

SELECT bqutil.td.nullifzero(0)

UDFs

• ascii
• index
• last_day
• months_between
• nullifzero
• zeroifnull

Documentation

ascii(string_expr STRING)

Returns the decimal representation of the first character in the string_expr. Teradata docs

SELECT bqutil.td.ascii('y')

121

index(string_expr1 STRING, string_expr2 STRING)

Returns the 1-based index of the first occurrence of string_expr2 inside string_expr1. Teradata docs

SELECT bqutil.td.index('BigQuery', 'Query')

4

last_day(date_expr DATE)

Returns the date of the last day in the month of the date_expr. Teradata docs

SELECT bqutil.td.last_day('2019-07-05')

2019-07-31

months_between(date_expr1 DATE, date_expr2 DATE)

Returns the number of months between date_expr1 and date_expr2. Teradata docs

SELECT bqutil.td.months_between('2019-01-01', '2019-07-31')
  , bqutil.td.months_between('2019-07-31', '2019-01-01')

-6, 6

nullifzero(expr ANY TYPE)

Returns NUll if the expr evaluates to 0. Teradata docs

SELECT bqutil.td.nullifzero(NULL)
  , bqutil.td.nullifzero(0)
  , bqutil.td.nullifzero(1)

NULL, NULL, 1

zeroifnull(expr ANY TYPE)

Returns 0 if the expr evaluates to NULL. Teradata docs

SELECT bqutil.td.zeroifnull(NULL)
  , bqutil.td.zeroifnull(0)
  , bqutil.td.zeroifnull(1)

0, 0, 1