Query Syntax Error: There was an error with the expression. 1: near "ESCAPE": syntax error

Product(s): Bentley WaterGEMS, SewerGEMS, CivilStorm, StormCAD, PondPack, StormCAD, HAMMER, WaterCAD
Version(s): V8i
Area:

Other

Problem Description

When I go to run a query I get the following error message: "Query Syntax Error: There was an error with the expression. 1: near "ESCAPE": syntax error"

Background

The problem was found using a SQL statement with two % wildcard characters in it. The original statement was:

Physical_PipeMaterial LIKE '%PN%'. This statement causes a problem, however, because it does not use an escape character to let SQL know to stop looking for a unlimited amount of characters before it gets to the 'PN%' part of the string. Using a escape character let's SQL's logic know when to stop.

Steps to Resolve

Option 1 - Use the proper SQL syntax with an escape sequence

Something like this should work:  Physical_PipeMaterial LIKE '%/PN%' ESCAPE '/'

Option 2 - Uncheck the "Validate on OK" checkbox before you hit ok

See Also

Query not returning results for fields that use a pick-list 

Recommended
Related