This example shows how to create a method, and then an alias for that method, to act like the Sybase-style SUBSTRING built-in function. (Note that you can also perform methods of java.lang.String on any string within SQL-J; simply select STRINGVALUE.substring(). This example just shows what to do in order to keep using your favorite syntax).

Make a class with a static method returning the requested substring to perform the function.

  import java.sql.*;public class MyBuiltIns{public static String substring(java.lang.String theString,int start, int length){if (theString.length() > start+length-1){return theString.substring(start-1,start+length-1);}else{return theString.substring(start-1, theString.length());}}

Compile the file and make sure that the class is in the CLASSPATH environment variable. Add the method as an alias.

  CREATE METHOD ALIAS substr FOR MyBuiltIns.substring 

Enter SQL like:

  SELECT substr(name, 1, 5) FROM customers

This is the same as:

  SELECT name.substring(0,4) FROM customers