>it has to exact match "100" yet there may be spaces in the field so I believe it's necessary.
The way I posted is for exact match no matter how many trailing spaces exist (not spaces on start but at end). ie:
lcCust_id1 = "ALFKI"+space(100)
lcCust_id2 = "ALFKI"
select * from customer where cust_id == lcCustId1
select * from customer where cust_id == lcCustId2
select * from customer where left(trim(cust_id),5) = lcCustId2
All return the same result (for ALFKI only even if there where a ALFKIA)
For nonexact match you can use = operator instead of == operator (== is ANSI setting independant).
Cetin