Migrating Built-In SQL Functions

If you have built-in SQL functions from another RDBMS that do not map to an Vertica-supported function, you can migrate them into your Vertica database by using a user-defined SQL function.

The example scripts below show how to create user-defined functions for the following DB2 built-in functions:

UCASE()

This script creates a user-defined SQL function for the UCASE() function:

=> CREATE OR REPLACE FUNCTION UCASE (x VARCHAR)
   RETURN VARCHAR 
   AS BEGIN 
   RETURN UPPER(x);
   END;

LCASE()

This script creates a user-defined SQL function for the LCASE() function:

=> CREATE OR REPLACE FUNCTION LCASE (x VARCHAR) 
   RETURN VARCHAR 
   AS BEGIN 
   RETURN LOWER(x);
   END;

LOCATE()

This script creates a user-defined SQL function for the LOCATE() function:

=> CREATE OR REPLACE FUNCTION LOCATE(a VARCHAR, b VARCHAR)
   RETURN INT
   AS BEGIN
   RETURN POSITION(a IN b);
   END;

POSSTR()

This script creates a user-defined SQL function for the POSSTR() function:

=> CREATE OR REPLACE FUNCTION POSSTR(a VARCHAR, b VARCHAR)
   RETURN INT
   AS BEGIN
   RETURN POSITION(b IN a);
   END;