String functions in SP/trigger language

( Note that Firebird 1.5 already has builtin SUBSTRING function. )
( Note that Firebird 2.0 already has builtin TRIM, BIT_LENGTH, CHAR_LENGTH, OCTET_LENGTH functions. )

TrimRight
Truncating string
Length of string function
Position of substring function
Substring function

All these functions can easily be implemented by UDF, however in some situations you can't/don't want to use UDF. Among reasons to avoid UDFs are e.g.

UDFs are not supported on NetWare
UDFs are platform dependent
missing UDF can prevent doing database restore
UDF has no info about character set of its parameters
UDFs can't return <null>
incorrectly written UDF can crash server

TrimRight in SP

InterBase does not have built-in function for trimming trailing spaces. Such function can be useful e.g. to convert CHAR string to VARCHAR when importing data from external files. rtrim function is e.g. part of the standard UDF library ib_udf.dll, but sometimes you want to avoid using UDFs. Unless you need to use it on very long strings (e.g. CHAR(30000)), it is possible to implement trim function as stored procedure.

When you CAST string to shorter one, the statement will succeed if removed characters are spaces, or it will fail (raise exception) if you try to remove non-blank characters. So just trying to CAST the string to shorter and shorter string and trapping errors will do the trick. It can be called either as a stored procedure (EXECUTE PROCEDURE TrimRight 'abc') or as a select procedure (SELECT ... FROM TrimRight('abc') ).

CREATE PROCEDURE TrimRight (str VARCHAR(10))
  RETURNS (ret VARCHAR(10)) AS
BEGIN
  ret = str;
  IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
  IF (str = '')    THEN BEGIN ret = ''; SUSPEND; EXIT; END
  BEGIN
    ret = CAST (str AS char(9));
    ret = CAST (str AS char(8));
    ret = CAST (str AS char(7));
    ret = CAST (str AS char(6));
    ret = CAST (str AS char(5));
    ret = CAST (str AS char(4));
    ret = CAST (str AS char(3));
    ret = CAST (str AS char(2));
    ret = CAST (str AS char(1));
    SUSPEND;
    WHEN ANY DO SUSPEND;
  END
END

Examples:

  SELECT '>' || ret || '<'
    FROM TrimRight (null)
  ============
  <null>


  SELECT '>' || ret || '<'
    FROM TrimRight (' 1234      ')
  ============
  > 1234<


  EXECUTE PROCEDURE TrimRight '1234      '
  ==========
  1234

Example of calling from another stored procedure:

  EXECUTE PROCEDURE TrimRight str_in
          RETURNING_VALUES    str_out;

Notes:

It is not possible to use WHILE (...) DO loop to simplify the code because CAST can't contain variable in place of char length (i.e. CAST(str AS CHAR(:len))).

There is a bug in IB5.1/5.6 (corrected in IB6) - if you remove inner BEGIN/END parenthesis, then SELECT ... FROM TrimRight(null); and SELECT ... FROM TrimRight(''); will return two rows instead of one.

Truncating string in SP

Unlike trimming, truncating is a function that will shorten string regardless of its contents, i.e. it will remove even non-blank characters. There is no such built-in function in Interbase (except external UDF). When CASTing longer string as shorter one InterBase will raise exception "... string truncation". When assigning longer string directly into shorter variable, InterBase will raise exception too, but the truncated value will be assigned anyway ! All we need to do is trap the exception by WHEN ANY DO statement. Here is example procedure that truncate string down to 5 characters:

CREATE PROCEDURE Trunc10To5 (a varchar(10))
  RETURNS (ret varchar(5)) AS
BEGIN
  ret = '';
  ret = a;
  WHEN ANY DO EXIT;
END

Command

  EXECUTE PROCEDURE Trunc10To5 '1234567890'

will return '12345'.

Note that you must not use CAST, and that variable you are assigning to must not contain <null>, so these two procedures will not work:

  CREATE PROCEDURE test1 (a varchar(10))
    RETURNS (ret varchar(5)) AS
  BEGIN
    ret = null;
    ret = a;
    WHEN ANY DO EXIT;
  END

  CREATE PROCEDURE test2 (a varchar(10))
    RETURNS (ret varchar(5)) AS
  BEGIN
    ret = CAST(a AS VARCHAR(5));
    WHEN ANY DO EXIT;
  END

Also note that it is probably a bug that value is assigned even if exception is raised; however it is the way how IB4, IB5 and IB6 work.

Length of string function in SP

Because Length function does not modify input string (like trimming and truncating), the implementation using WHILE loop and LIKE test is straightforward:

CREATE PROCEDURE Len (str VARCHAR(100))
  RETURNS (len INTEGER) AS
DECLARE VARIABLE pat VARCHAR(100);
BEGIN
  len = null;
  IF (str IS NULL) THEN EXIT;

  pat = '';
  len = 0;
  WHILE (NOT str LIKE pat) DO BEGIN
    pat = pat || '_';
    len = len + 1;
  END
END

You can omit "len = null;" because variables are initialized to null automatically. The length will be counted including trailing spaces.

  EXECUTE PROCEDURE Len null
      LEN 
  ======= 
   <null>

  EXECUTE PROCEDURE Len ''
      LEN 
  ======= 
        0 

  EXECUTE PROCEDURE Len 'abc'
      LEN 
  ======= 
        3 

  EXECUTE PROCEDURE Len 'xyz   '
      LEN 
  ======= 
        6

Position of substring function in SP

This function returns index of the first character in a specified substring (SubStr parameter) that occurs in a given string (Str).

CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
  RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
  IF (SubStr IS NULL OR Str IS NULL)
  THEN BEGIN Pos = NULL; EXIT; END

  SubStr2 = SubStr || '%';
  Tmp = '';
  Pos = 1;
  WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
    SubStr2 = '_' || SubStr2;
    Tmp = Tmp || '_';
    Pos = Pos + 1;
  END

  IF (Str LIKE Tmp) THEN Pos = 0;
END

Tmp variable is used to stop the loop if number of iterations is equal to Str length. Because SubStr is used on right side of LIKE operator, it should not contain SQL wildcards, i.e. '_' and '%' (unless you use ESCAPE clause). If substring is not found, return value is zero.

  EXECUTE PROCEDURE Pos 'ab', 'abcdefghij'
      POS 
  ======= 
        1 

  EXECUTE PROCEDURE Pos 'cd', 'abcdefghij'
      POS 
  ======= 
        3 

  EXECUTE PROCEDURE Pos 'x', 'abcdefghij'
      POS 
  ======= 
        0

Substring function in SP

I will leave this as homework for esteemed readers.
The support for SUBSTRING function exists in InterBase for a long time on BLR level. It is not much difficult to edit SP's BLR to enable this functionality.