Using the LIKE and IN Operators with queries to filter on text fields such as label

Applies To 
Product(s): WaterGEMS, WaterCAD, HAMMER, SewerCAD, SewerGEMS, StormCAD, CivilStorm
Version(s): CONNECT Edition, 08.11.05.XX+
Area:  Modeling


How can I perform advanced SQL queries in the query builder, to filter based on information in a text field? For example to retrieve all pipes that begin with "P-2", or does not contain a certain word or letters, or only the pipes with specific materials (from a list).


Although there may not be buttons at the top portion of the query builder for all supported operators, many can still be used.

The LIKE Operator

The LIKE operator can be used in a query to filter based on a specific word or text contained within a field such as label.

In a Flextable, right click on the Label column heading and choose Filter > Custom. Now create the following filter, either by typing it into the text box, or by double clicking on selection fields:

Label LIKE '%word%'

You may use the % symbol as a wildcard on the either the end of the query, instead of on both sides as shown above, and the utilization of "word" can be any choice of characters you would like.

Note that the single quote is used.

The same syntax can be used with other queries, such as a regular query to select elements in the drawing or to create a selection set.

You can also use "NOT LIKE" in a similar fashion. For example if you want to select all pipes whose label does not contain the phrase P-, then you would use this:

Label NOT LIKE '%P-%'

The IN Operator

The IN Operator can be used in a query to filter based on a specific list. For example if you use three different materials for pipes but only want to see those that are using two of those three materials, you can use the IN operator to specify that list. The list will be in parenthesis using the single quote character to denote the value. Values in the list are case sensitive. Note: Starting with version of WaterGEMS, WaterCAD, HAMMER, and WaterOPS, an IN button has been added to the Query Builder. For the storm-sewer products or for earlier versions, you can use the following syntax:

<fieldname> IN ('Value1','Value2')

Here is an example where we want to filter a pipe FlexTable to only show pipes with material of "Ductile Iron" or "PVC":