vertica

Vertica UDFs

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

UDFs

• lowerb
• substrb
• upperb

Documentation

lowerb(string STRING)

This function lower cases all ascii characters in string which imitates the Vertica function lowerb. Note that BigQuery's lower will lower case all non ascii characters.

   SELECT ve.lowerb('ÉTUDIANT'), lower('ÉTUDIANT'); 
   SELECT ve.lowerb('Student'), lower('Student');

Étudiant, étudiant, student, student

substrb(string STRING, position INT64, length INT64)

This function treats a string as an array of bytes and takes the substring based on the octets. The function imitates the Vertica function substrb. Note that BigQuery's substr does not treat the string as bytes.

   SELECT ve.substrb('soupçon', 5, 2), substr('soupçon', 5, 2); 
   SELECT ve.substrb('something', 1, 2), substr('something', 1, 2);

ç, ço, so, so

upperb(string STRING)

This function upper cases all ascii characters in string which imitates the Vertica function lowerb. Note that BigQuery's upper will upper case all non ascii characters.

   SELECT ve.upperb('étudiant'), upper('étudiant'); 
   SELECT ve.upperb('Student'), upper('Student');

éTUDIANT, ÉTUDIANT, STUDENT, STUDENT