Known Issue: parameters with percent sign in query strings not working (e.g. LIKE clause)

Problem

SQL statements/queries with a percent sign in a string are not transformed correctly. In most cases, parameters (starting with a colon) will not be detected (or the statement will just not retrieve any results).

Examples:

SELECT Name, Population
FROM Country
WHERE Continent LIKE '%:Continent%'

CODE

SELECT *
IN x
WHERE x.y = '%:xparam%'

CODE

Solution

This has been a long-lived problem that is technically not easy to fix. The workaround is to use a string concatenation function available in your SQL dialect. In the case of MySQL, for example, the above examples will work like this:

SELECT Name, Population
FROM Country
WHERE Continent LIKE CONCAT('%', :Continent, '%')

CODE

SELECT *
IN x
WHERE x.y = CONCAT('%', :xparam, '%')

CODE

Another workaround that might work for your use-case would be to use the parameter type `Constant` for your parameter (see "Parameter Types" section in Queries manual).

Problem

Solution

More Info