Home General
New Blog Post: Email: Multi-Service

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

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.
Kind regards,

Jeroen Röttink
JR-soft software solutions

Comments

  • 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.


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
Sign In or Register to comment.