Use SQLBuilder to add unaccent() ILIKE unaccent() criteria in PostgreSQL

jeroenrottink

Hi TeamDM,

I would like to add a criteria by code to an existing DADE query. I thought TdaSQLBuilder.SearchCriteria.AddExpression() would do the job but I have some questions.
The database is PostgreSQL.
The criteria that has to be added is: unaccent(employee.naam) ILIKE unaccent('%rot%')
AddExpression() cannot use ILIKE (case insensitive LIKE) as an operator. Is it possible to add this?
When I change ILIKE into LIKE the query is build but funny things happen to the value:
I seems the value is examined (fieldname?) and based on the content
• The complete value is quoted resulting in an invalid query
• A wildcard character % is added. Doesn’t seem a problem when one is present.
Can this be turned off?
Or is there another way to accomplish this without using manual SQL?

The hack I can use but don't like, is calling AddExpression() with
aExpression: unaccent(employee.naam) ILIKE unaccent('%rot%') ) -- YES including comment chars
aOperator: =
aValue: ''

Kind of SQL injection.

nardmoseley

Hi Jeroen,

RB supports standard SQL search operators.

ILKE is proprietary to PostgreSQL. The standard SQL equivalent is

Upper(fieldname) Like Upper(value)

Example using TdaSQLBuilder

  lSQLBuilder := TdaSQLBuilder.Create(ppReport1.DataPipeline);

  lsExpression := 'Upper(Customer.Company)';
  lsValue := 'Upper(''%Un%'')';
  lSQLBuilder.SearchCriteria.AddExpression(lsExpression, 'Like', lsValue);

  lSQLBuilder.Free;

Unaccent function is proprietary to PostgreSQL. I do not have PostgreSQL so did not test.

Try nesting the Unaccent function calls

  lSQLBuilder := TdaSQLBuilder.Create(ppReport1.DataPipeline);

  lsExpression := 'Upper(Unaccent (Customer.Company))';
  lsValue := 'Upper(Unaccent(''%Un%''))';
  lSQLBuilder.SearchCriteria.AddExpression(lsExpression, 'Like', lsValue);

  lSQLBuilder.Free;

To optimize performance, consider adding fields to the database tables that contain the upper case, unaccented data values.