Delphi TClientDataSet Filtering using LIKE and foCaseInsensitive

Question

Using Delphi XE, I'm trying to do some filtering on a TClientDataSet and I'm unable to get the case-insensitive filtering to work properly in conjunction with a LIKE operator

Consider this code which does the filtering

cdsDocs.DisableControls;
try
  cdsDocs.Filtered := False;
  cdsDocs.FilterOptions := [foCaseInsensitive];
  cdsDocs.Filter := 'Product LIKE ''%' + txtFilter.Text + '%''';
  cdsDocs.Filtered := True;
finally
  cdsDocs.EnableControls;
end;

and consider that the Product field of this dataset contains only the values 'b' and 'B'.

1. when (txtFilter.Text = 'b') I only get 'b's (I'm expecting 'b's and 'B's)
2. when (txtFilter.Text = 'B') I only get 'B's (likewise, I'm expecting 'b's and 'B's)

It seems as though the LIKE '%b%' and the foCaseInsensitive don't work together? What should I do to make it work? I read the documentation but I can't find my mistake (if any). TIA.

Solution

Everything was working fine until I tried to extend the filtering to allow for case-insensitive searches (I tried to use the existing FilterOption foCaseInsensitive), now suddenly I have a bug? No, that doesn't make sense to me. I've decided to achieve the desired case-insensitive filtering another way, and keep my ego intact.

Here's the revised code (works perfectly)

cdDocs.DisableControls;
try
  cdDocs.Filtered := False;
  cdDocs.FilterOptions := [];

  if (cbCaseSensitive.Checked) then
    cdDocs.Filter := 'Product LIKE ''%' + txtFilter.Text + '%'''
  else
    cdDocs.Filter := 'UPPER(Product) LIKE ''%' + UPPERCASE(txtFilter.Text) + '%''';

  cdDocs.Filtered := True;
finally
  cdDocs.EnableControls;
end;

BTW, this is connecting to an Interbase/Firebird database using the Interbase DB Access components that ship with Delphi XE. If connecting to a different DB you may need to replace the "UPPER" function with the equivalent function for your chosen RDBMS (whatever you would use in the equivalent SQL WHERE clause)

Comments

[Darkendorf]

thanks for this, but in my case (ibdac TIBCTable with firebird 2.5.2) i get following exception : "field UPPER not found"... whatever i do : "(UPPER(UTF8 Product collate fr_fr) LIKE ''%'+str+'%'')" upper not found too. It seems it rejects me from using any operations on fields...