Using LIKE statement for filtering

Question

Im using this code to filter my table:

Table.Filtered := False;
Table.Filter := '[' + Field_Search + '] LIKE ''%' + Edit_Search.Text + '%''';
Table.Filtered := True;

but it raises this exception:

"Operation not applicable."

where is problem?

Ken White · Accepted Answer · 2012-07-21T20:30:11.977

12

A TTable.Filter isn't a SQL query. LIKE isn't supported (neither is IN). The supported operators are =, <>, >, <, >=, '<=,AND,NOTandOR`, according to the documentation

For more complicated filtering, use the TDataSet.OnFilterRecord event:

procedure TForm1.Table1FilterRecord(Dataset: TDataset; var Accept: Boolean);
begin
  // Don't remember if D7 supports DataSet[FieldName] syntax; if not,
  // use DataSet.FieldByName instead, or a persistent field.
  Accept := Pos(Edit_Search.Text, DataSet[SearchField].AsString) > 0;
end;

edited Jul 21 '12 at 20:30

answered Jul 21 '12 at 18:34

Ken White

123,280
14
225
444

4

LIKE is supported in dataset filters. http://docwiki.embarcadero.com/Libraries/en/Data.DB.TDataSet.Filter – Bruce McGee Jul 21 '12 at 19:56
1

It's not, according to the [list of supported logical operators](http://docwiki.embarcadero.com/RADStudio/en/Setting_the_Filter_Property) – Ken White Jul 21 '12 at 20:27
I think Ken is right. The like operator is supported by some databases, such as DBISAM (TDBISAMTable), but not TTable. – John Easley Jul 21 '12 at 21:50
You're right. When using the BDE, TTable doesn't support LIKE in filters and gives the same message that the OP reported. He would either need to use another dataset or use a clientdataset with his current setup. – Bruce McGee Jul 21 '12 at 23:42
2

@Bruce: Or use the code I posted in my answer, which doesn't require as much work as changing to another dataset or a clientdataset. – Ken White Jul 21 '12 at 23:56

score 4 · Answer 2 · answered May 26 '13 at 04:02

4

Table.Filtered := False;
Table.Filter := Field_Search + ' LIKE ' + QuotedStr('*' + Edit_Search.Text + '*');
Table.Filtered := True;

answered May 26 '13 at 04:02

Dodi Ariyanto

41
1

2

You should add some details to your answer! – Alexandre Lavoie May 26 '13 at 04:20

Bravesaw · Answer 3 · 2018-04-10T17:41:29.013

you should use this :

   DataModule.Table.Filtered := False;
   DataModule.Table.Filter := 'Field_Name' + ' LIKE ' + QuotedStr(Edt_SearchByCode.Text +'%');
   DataModule.Table.Filtered := True;

and will work like a Magic and no use of TQuery any more .... and if you want Matching does not take case-sensitivity into account. you should use this code instead:

   DataModule.Table.Filtered := False;
   DataModule.Table.FilterOptions := [foCaseInsensitive];
   DataModule.Table.Filter := 'Field_Name' + ' LIKE ' + QuotedStr(Edt_SearchByCode.Text +'%');
   DataModule.Table.Filtered := True;

Asad Alamdar · Answer 4 · 2017-11-17T20:34:32.703

1

The following Code will work as Like as well:

if Edit1.Text <>'' then
begin
Query1.Filter :='FieldName ='+quotedstr('*'+ edit1.Text +'*');
Query1.Filtered:=true;
end
else
begin query1.Filtered :=false; end;

edited Nov 17 '17 at 20:34

answered Oct 30 '17 at 10:18

Asad Alamdar

158
8

score 1 · Answer 5 · answered Nov 12 '21 at 12:24

I found this topic looking for something else. I always use the TDataSet.OnFilterRecord event, and in most cases I use a TEdit.OnChange event so the search is "Active";

var
Criteria : string;
...
...

procedure TForm1.Edit1Change(Sender: TObject);
begin
  Table.Filtered := False;
  Criteria := Edit1.text;
  Table.Filtered := True;
end;

and with the OnFilter event

procedure TForm1.TableFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Accept := AnsiContainsStr(Table.Fields[xx].asString,Criteria);
end;

Works for me.

score 0 · Answer 6 · answered Jul 26 '19 at 08:39

I am using mostly with TEdit here is the code

if (EditSerarch.Text <> '') then
begin
FDQQuery.Filtered := false;
FDQQuery.Filter:=   'Name LIKE'+ QuotedStr('%'+EditSerarch.Text+'%') +
            ' OR Company LIKE '+ QuotedStr('%'+EditSerarch.Text+'%') +
              ' OR Phone LIKE '+ QuotedStr('%'+EditSerarch.Text+'%') +
             ' OR Mobile LIKE '+ QuotedStr('%'+EditSerarch.Text+'%');
FDQQuery.Filtered:= True;
end else FDQQuery.Filtered := false;

@kobik: Oops, my mistake, comment deleted. Thanks] – MartynA Jul 28 '19 at 15:04 — MartynA, Jul 28 '19 at 15:04

score -1 · Answer 7 · edited Nov 01 '13 at 16:53

-1

I recommend to use a SQL Query to perform this operation.

Example:

query1.SQL.Text:='Select * FROM table_name WHERE field like '+ QuotedStr(edit1.text+'%');
query1.Active:=true;

edited Nov 01 '13 at 16:53

ProgramFOX

6,131
11
45
51

answered Nov 01 '13 at 16:35

user2945655

1

Using LIKE statement for filtering

7 Answers7

Linked