Search code examples
sqldelphifiredac

Delphi select SQL to find & in rows

I am running a query to find the rows containing the character "&" and using dbgrid to show the result.

I have tried to write query as it works in SQL Server or MYSQL

FDQuery1.SQL.Text := 'SELECT * FROM DATA WHERE NAME LIKE ''%&%''';  
FDQuery1.Open();

Instead of getting the result containing the character "&" in NAME column, it's selecting all the rows where NAME column is not blank

Other queries are working when I am finding "$" in place of "&" - like this:

FDQuery1.SQL.Text := 'SELECT * FROM DATA WHERE NAME LIKE ''%$%''';

This query selecting the rows containing the $ character

It's only selecting all not null rows when I am using this condition: %&%.

Solution

  • FireDAC has a query preprocessing / macro capability to allow an extra layer of modification of queries. Some of the functionality uses the & symbol which means either extra care needs to be taken when writing queries or disabling macros. It can be turned off at the connection or query level.

      FDConnection1.ResourceOptions.MacroExpand := false;
  FDQuery1.ResourceOptions.MacroExpand := false;

    Substitution Variables

    ‘&’ symbol and is followed by the macro variable name. For example:

    SELECT * FROM &TabName

    The symbols have the following meaning:

    ‘!’--“string” substitution mode. The macro value will be substituted “as is”, directly into the command text without any transformation.

    ‘&’-–“SQL” substitution mode. The macro value will be substituted depending on the macro data type, using target DBMS syntax rules. To use the macros, use the following code snippet:

    FDQuery1.SQL.Text := 'SELECT * FROM &TabName';
FDQuery1.MacroByName('TabName').AsRaw := 'Orders';
FDQuery1.Open;

    The macros are processed when ResourceOptions.MacroCreate and MacroExpand are set to True.

    Ref: Preprocessing Command Text (FireDAC)