ESQL

 CREATE FUNCTION padding(IN string CHAR, IN length1 INT ) RETURNS CHAR
   -- This Function will pad a string to a specific length. There are 2 cases:
   -- String too short: add spaces:   'abcd'         --> 'abcd  '
   -- String too long: truncate:      'abcdefghijkl'    --> 'abcdef'
   BEGIN
      DECLARE padding CHAR;
      IF LENGTH( string ) <= length1 THEN         -- 'Batch', 10 --> 'Batch     '
         SET padding = LEFT( string || SPACE( 1000 ), length1 );
      ELSE
         SET padding = string;       -- 'ItemDescription', 10 --> 'ItemDescri'
      END IF;
      RETURN padding;
   END;

   CREATE FUNCTION crlf() RETURNS CHAR   BEGIN
      RETURN CAST( X'0D0A' AS CHAR CCSID 819 );   -- Carriage Return, Line Feed (Linux&Windows)
   END;
   CREATE FUNCTION lf() RETURNS CHAR BEGIN
      RETURN CAST( X'0A' AS CHAR CCSID 819 );      -- Line Feed (Linux only)
   END;

   CREATE FUNCTION crlfCcsidEncoding(IN ccsid INT, IN encoding INT) RETURNS CHAR
   BEGIN
      -- For Linux&Windows: '0D0A',
      -- '0D' for Windows only
      RETURN CAST( X'0D0A' AS CHAR CCSID ccsid ENCODING encoding );
   END;

   CREATE FUNCTION selectSubField(sourceString CHAR, delimiter CHAR, targetStringPosition INT) RETURNS CHAR
   -- This function returns a substring at parameter position TargetStringPosition within the
   -- passed parameter SourceString.  An example of use might be:
   -- selectSubField(MySourceField,' ',2) which will select the second subfield from the
   -- field MySourceField delimited by a blank.  If MySourceField has the value
   -- "First Second Third" the function will return the value "Second"
   BEGIN
      DECLARE delimiterPosition INT;
      DECLARE currentFieldPosition INT 1;
      DECLARE startNewString INT 1;
      DECLARE workingSource CHAR sourceString;
      SET delimiterPosition = POSITION( delimiter IN sourceString );
      WHILE currentFieldPosition < targetStringPosition AND delimiterPosition <> 0 DO
         SET startNewString = delimiterPosition + LENGTH( delimiter );
         SET workingSource = SUBSTRING( workingSource FROM startNewString );
         SET delimiterPosition = POSITION( delimiter IN workingSource );
         SET currentFieldPosition = currentFieldPosition + 1;
      END WHILE;
      IF currentFieldPosition < targetStringPosition THEN
         SET workingSource = NULL;
      ELSEIF delimiterPosition > 0 THEN
         -- Remove anything following the delimiter from the string
         SET workingSource = SUBSTRING( workingSource FROM 1 FOR delimiterPosition - 1 );
      END  IF;
      RETURN workingSource;
   END;

   CREATE FUNCTION countCharInChar(IN char1 CHAR, IN char2 CHAR) RETURNS INT
   -- Returns the number of occurences of char1 in char2
   -- E.g.:     ('A', 'ABCDABCDABCD')    --> 3
   --           ('ABC', 'ABCDABCD')    --> 2
   --         ...
   BEGIN
      DECLARE count INT 0;
      IF char1 IS NOT NULL AND char2 IS NOT NULL THEN
         DECLARE position1 INT POSITION( char1 IN char2 REPEAT count+1 );
         WHILE position1 <> 0 DO
            SET count = count + 1;
            SET position1 = POSITION( char1 IN char2 REPEAT count+1 );
         END WHILE;
      END IF;
      RETURN count;
   END;