Using Trim in ClientDataSet Filters

I announced in my last blog post that an updated version of Delphi in Depth: ClientDataSets is now available, both in printed and in eBook formats. In response, a reader posted this question as a comment:

I used TClientDataSet.Filter. CDS's Filter support "Trim" statement in Delphi help doc. It support "Trim(Name, '.')". But, how to used the statement?

Here is the answer. Imagine that your ClientDataSet includes a Company field, and that you have set the ClientDataSet's Filtered property to True, and have entered a Trim statement in the Filter property.

When you call Trim you can pass either one or two parameters. If you pass one parameter, you pass the name of a field. In response, Trim will remove any leading and trailing white spaces (please see the first comment below). For example, the filter Trim(Company) = 'Unisco' will trim white space, and the filtered data will include records whose Company field includes 'Unisco ', '    Unisco', '  Unisco  ', and 'Unisco'.

If you include the second, optional parameter, Trim will return the value of that field after removing that leading and/or trailing character. As a result, Trim(Company, 'o') = 'Unisc' will match on 'Unisco', 'oUnisc', and 'oUnisco. Likewise, Trim(Company, '.') = 'Unisco' will match on 'Unisco', '.Unisco', 'Unisco....', and '...Unisco.'.

There are two variations on Trim. These are TrimLeft and TrimRight. Like Trim, these functions can also take one or two parameters. If you call TrimLeft or TrimRight with only one parameter, those functions will remove white space from the beginning or the ending of the specified field, respectively. If you pass a second parameter, TrimLeft or TrimRight will remove that character from the beginning or ending of the specified field, respectively.

For example, TrimLeft(Company, 'U') = 'nisco' will match on a record whose Company field contains 'Unisco' and 'nisco'. Likewise, TrimRight(Company, 'o') = 'Unisc' will match on a record whose company field contains 'Unisc', 'Unisco', and even 'Uniscooo'.