String functions in SP/Trigger language

Source: Ivan Prenosil's InterBase site

TrimRight, Truncating string, Length of string, Position of substring function.

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

• 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
• incorrectly written UDF can crash server

TrimRight in SP

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

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


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


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

  EXECUTE PROCEDURE TrimRight str_in
          RETURNING_VALUES    str_out;

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

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

  EXECUTE PROCEDURE Trunc10To5 '1234567890'

Note that you must not use CAST, and that variable you are assigning to must not contain , 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

Length of string function in SP

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

  EXECUTE PROCEDURE Len null
      LEN 
  ======= 
   

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

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

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

Position of substring function in SP

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

  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