Question

Is there a way I can use 'like' in an API query

I would expect to do something like this:

CSelectQuery query = api.Database.NewSelectQuery("Attachment");
query.AddWhere("Attachment.sFileName like '%.zip'");

But I get the error:

System.ArgumentException: Invalid syntax: expected boolean expression, found 'like'

Is there some other way to use like?

Case FC1912730

Answer 1

The following utility functions have been added to the CSelectQuery class (and any other query classes inheriting from CWhereQuery) in FogBugz 8:

''' <summary>
''' Add a wildcard-free LIKE condition to the query.
''' The search parameter will be encoded and escaped for sql injection and for use in a LIKE clause.
''' </summary>
''' <param name='sColumn'>The column on which to perform the like search.</param>
''' <param name='sLike'>The substring to search for.</param>
Public Sub AddLike(sColumn, sLike)

''' <summary>
''' Add a substring LIKE condition to the query.
''' A percent sign will be added to the beginning and end of the search term.
''' The search parameter will be encoded and escaped for sql injection and for use in a LIKE clause.
''' </summary>
''' <param name='sColumn'>The column on which to perform the like search.</param>
''' <param name='sLike'>The substring to search for.</param>
Public Sub AddSubstringLike(sColumn As String, sLike As String)

''' <summary>
''' Add a prefix LIKE condition to the query.
''' A percent sign will be added to the end of the search term.
''' The search parameter will be encoded and escaped for sql injection and for use in a LIKE clause.
''' </summary>
''' <param name='sColumn'>The column on which to perform the like search.</param>
''' <param name='sLike'>The prefix to search for.</param>
Public Sub AddPrefixLike(sColumn As String, sLike As String)

''' <summary>
''' Add a suffix LIKE condition to the query.
''' A percent sign will be added to the beginning the search term.
''' The search parameter will be encoded and escaped for sql injection and for use in a LIKE clause.
''' </summary>
''' <param name='sColumn'>The column on which to perform the like search.</param>
''' <param name='sLike'>The suffix to search for.</param>
Public Sub AddSuffixLike(sColumn As String, sLike As String)

Answer 2

I guess this answers my question:

Because queries generated with CQuery must work on all FogBugz-supported database platforms, SQL syntax that is inconsistent between platforms is not currently supported. This means that "LIKE" and "IIF" clauses are disallowed.

https://developers.fogbugz.com/default.asp?W83