How to preserve white spaces in function arguments in Informix?

Question

I'm trying to create a version of InStr function for Informix 11.5. Here's the code:

CREATE FUNCTION MY_INSTR(txt VARCHAR(255), term VARCHAR(255))
    RETURNING VARCHAR(255);
  DEFINE i INTEGER;
  DEFINE len INTEGER;
  DEFINE lenterm INTEGER;

  LET i = 1;
  LET lenterm = LENGTH(term);
  LET len = LENGTH(txt) - lenterm;

  WHILE ( i <= len ) LOOP
    IF SUBSTR( txt, i, lenterm ) = term THEN
      RETURN i;
    END IF
    LET i = i + 1;
  END LOOP

  RETURN 0;
END FUNCTION;

I'll use this function to get the first name of a user in a column with the full name, like this:

SELECT SUBSTR(name, 1, MY_INSTR(name, " ")) AS firstname
  FROM users

I need to pass the value " " as the term, but when I do, lenterm equals 0.

Is there a way to preserve white spaces when passing them to a function in Informix?

Answer 1

For VARCHAR types, the OCTET_LENGTH function should return the length including trailing spaces. From the manual:

The OCTET_LENGTH returns the number of bytes in a character column, including any trailing spaces.

I didn't have an informix 11.50 instance to test, but tried the following in an Informix 12.10 (FC6DE):

CREATE FUNCTION f_my_t(param1 VARCHAR(10))
RETURNING INTEGER AS param1_length;
  DEFINE l_result INTEGER;
  LET l_result = OCTET_LENGTH(param1);
  RETURN l_result;
END FUNCTION;

EXECUTE FUNCTION f_my_t (' ');

The result I got:

Database selected.
Routine created.

param1_length
            1
1 row(s) retrieved.

Database closed.