""kamiller42"" <kyle@...> wrote in message
news:a6oome+5dkj@......

> I started using FreeUDF's trim function, mainly f_rtrim. I find it adding

characters, like 3 times the original characters. I am using against
Firebird 1.0 Final. Any know what's wrong? Here is a sample query that is
not working correctly.

>
> select R.RDB$RELATION_NAME
> ,f_rtrim(R.RDB$RELATION_NAME) DB_TABLE_NAME
> from RDB$RELATIONS R

What's the problem?

SQL> select R.RDB$RELATION_NAME||'.'
CON> , f_rtrim(R.RDB$RELATION_NAME)||'.' DB_TABLE_NAME
CON> from RDB$RELATIONS R;

See where the dots are:

TESTDEPENDS . TESTDEPENDS.
TESTDEPENDS2 . TESTDEPENDS2.
VKT . VKT.

In the first case, since the field is char, the dot is always aligned (it
can be observed clearly if you look at this with a fixed size font). In the
second case, since f_rtrim() was used, the dot appears immediately after the
first non-blank character.

Now your problem is what happens if you retrieve the field without
concatenating it with other field or constant:

SQL> select f_rtrim(R.RDB$RELATION_NAME) DB_TABLE_NAME
CON> from RDB$RELATIONS R;

You will notice the blanks at the tail again. And a lot of blanks. This is
not the UDF's fault. In their infinite and awesome wisdom, Borland chose to
convert the return of UDFs that is cstring() to SQL char instead of varchar.
I didn't want to change that for compatibility but unfortunately, the flaw
defeats several udfs. Any UDF returning cstring is affected. This is why I
decided to return varchar in fbudf. And if your udf says that it returns a
cstring(254), you will have 254 blanks minus the length of the non-blanks
characters. There are two solutions:

1) Concatenate your output with a zero-length string. This is two single
quotes without space between them:
SQL> select
CON> f_rtrim(R.RDB$RELATION_NAME) || '' as DB_TABLE_NAME
CON> from RDB$RELATIONS R;

2) Force the output to varchar, but varchar(254) since the UDF declares
cstring(254) as the return type or you may get truncation error messages:

SQL> select
CON> cast(f_rtrim(R.RDB$RELATION_NAME) as varchar(254)) as DB_TABLE_NAME
CON> from RDB$RELATIONS R;

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing