At 04:19 PM 11/08/2004 -0700, you wrote:

>I am converting a system from informix to firebird. I had to pull a
>bunch of data over from informix as chars.
>So I have a white space problem.
>
>select c_cid from cusmas where c_cid like '100' does not return a
>record but:
>select c_cid from cusmas where c_cid = '100' does return the 100 record.
>select c_cid from cusmas where c_cid like '100 ' (with spaces)
>returns a record.
>
>I have been able to do this type of thing with other engines:
>
>select c_cid from cusmas where trim(c_cid) like '100' and it will work
>because trim is removing trailing spaces.

This example isn't standard syntax for a LIKE comparison. LIKE is a
wildcard search and your argument has no wildcards. This statement ought
to be an equality search anyway, in which case you don't have to worry
about whitespace. A LIKE comparison considers white space, whereas an
equality comparison doesn't.

>So my question is (finally) how do I trim white space from a field?

1) in cases where you need a wildcard at the beginning, use CAST or the
RTRIM() external function for eliminating the white space from the compared
operand

select c_cid from cusmas where cast(c_cid as varchar(10)) like '%100'

select c_cid from cusmas where rtrim(c_cid) like '%100'

2) in cases where you only need a wildcard at the end, use STARTING WITH
instead of LIKE and avoid the LIKE semantics altogether:

select c_cid from cusmas where c_cid STARTING WITH '100'

3) where you want wildcards at both ends AND you are not concerned about
case-insensitivity, use CONTAINING instead of LIKE:

select c_cid from cusmas where c_cid CONTAINING '100'

/heLen