--- In firebird-support@yahoogroups.com, Roger Crämer <rcr@...> wrote:

>
> Thanks for the answer, but the suggestions to work around
> do not help me because
> o I have no influence on the db structure (it's not mine)
> o To pad the compare string at the end with blanks you need to
> to know the correct number of blanks at the end of the entry.
>
> The only workaround momentarily is to search for '%bcd%' and do
> some mor filtering on application level.

Hi Roger,

Your problem is due to the way the SQL standard defines comparisons
with char fields. The specification of a char field is that is will
space pad the end of the field until it is the defined length. The
like operator specification compares the whole field (including
trailing spaces) and as has been pointed out your record fails this
condition because it is actually 'abc '.

Whether you were to insert the value 'abc ' or 'abc', both are stored
as 'abc ', so there is no way to determine if something ends in
'bc' because the first case should not but the second case should be
returned.

If according to your specifications a record can not legitimately
contain a space at the end for the purposes of the query (so if you
insert the record 'abc ' you are happy that it returns with the like
'%bc' query), then you can use rtrim which is in ib_udf.dll that ships
with Firebird.

DECLARE EXTERNAL FUNCTION rtrim
CSTRING(255)
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';

select *
from tablea
where rtrim(somefield) like '%bc';

should do it.

So either the person who designed the table did not understand the
implications of using a char field, or you do not understand the
design decision they took. If you know it was the former, then the
field should probably be a varchar rather than a char. If not, then
you had better check what your query should be returning.

Adam