Migrating built-in SQL functions

If you have built-in SQL functions from another RDBMS that do not map to a 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;