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()
LCASE()
LOCATE()
POSSTR()
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;