--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- 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';
>

oops, that wont do anything because it will return as a char field
which has padded spaces at the end (my bad).


select *
from tablea
where rtrim(cast(somefield as varchar(10))) 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
>