> It seems to me that SUBSTRING does not work in triggers on
> strings in UNICODE_FSS character set.

Back when SUBSTRING was released FB v1.0 the release notes
said that:

"Because <pos> and <length> are byte positions, the identifier
can be a binary blob, or a sub_type 1 text blob with an
underlying one-byte-per-character charset. The function
currently does not handle text blobs with Chinese (2 byte/char
maximum) or Unicode (3 byte/char maximum) character sets. For
a string argument (as opposed to a blob), the function will
tackle ANY charset."

This appears to be reiterated in the FB v1.5 release notes.

> This work perfect for strings STRL that do not contain
> national characters. But STRL containing for example
> hungarian or czech characters causes conversion or overflow
> error and new record is not inserted.

There has been discussion recently suggesting that UNICODE_FSS
was always 3 bytes per character. My understanding (based on
documentation from Unix where unicode fss originated) was that
this character set was like UTF-8, but restricted to a maximum
of 3 bytes (for 16-bit unicode, whereas full UTF-8 can go to
6-bytes to support 32-bit unicode). The reason why the unix
implementation used variable character lengths was that 7-bit
ASCII strings could be used without change (which, incidently
is what Firebird does internally with its metadata string
fields that are declared as UNICODE_FSS).

Anyway, regardless of whether FB does 3-bytes per character
or a variable number of bytes per character you can see where
the fact that SUBSTRING operating on bytes is likely to cause
you problems.

**IF** UNICODE_FSS was fixed at 3 bytes per character then you
could work around the problem by multiplying your pos and
length parameters. BUT if UNICODE_FSS is truly variable (as
I believe it is) then SUBSTRING becomes essentially useless.
Which is one of the reasons I stopped trying to use it.

--
Geoff Worboys
Telesis Computing