InfoWater Boolean & Partial String DB Queries

Back to blog home

March 7, 2018 | Susana Macias-Marquez

In InfoWater a user can select network components and related data by creating logical query statements through the DB Query feature. A DB query allows the user to extract a set of records using logical statements on a field (or fields) stored within a specific database. Often used database query statements include boolean fields and searching a string in a field.

How to construct a Boolean DB Query

A Boolean field is intended to store a value of 1 or 0, where 1 corresponds to the term “Yes” and 0 to the term “No”.

InfoWater has built in boolean fields for check valves for pipe attributes and ‘allow overflow’ for tank attributes, nevertheless custom Boolean fields can be created in the InfoWater information tables for any of the InfoWater feature types:

When creating a DB query based on a Boolean field simply select the Boolean field. This query will search for facilities whose fields are true or Yes (1):

To find Boolean fields where the field is false or No (0), enter the operator “.NOT.” prior to Boolean Field:

How to search a partial string match in an InfoWater field using DB Query

There are three functions that prove helpful when searching a partial string in a string field. The following sections will show how the DB Query functions SUBSTR(), and the contain operator ($) can be used to find the following:

  • When a field contains the partial string,
  • The partial string is at the beginning of the string field
  • The partial string is found near the end of the string field
  • The partial string is at a specific part within the string field.

In this example the pipe IDs in the model contain the partial string ‘XY’ at different parts of the ID field. These queries were applied to the Domain and the Edit Domain Attributes tool was used to view the IDs of the facilities added to the Domain.

  1. To start let’s use the ‘Contain’ Operator ($) to find all ID fields that contain ‘XY’ partial string:

  1. Second find IDs that start with ‘XY’:

  1. Next use the SUBSTR() to find a partial string at the end of a string field:

  1. Finally, SUBSTR()to find partial string within the string field:

Find out more

Contact us

Tags: boolean query, partial string search, DB query

About the Authors

Susana Macias-Marquez

Susana Macias-Marquez

Technical Support Engineer


Susana provides expert support to customers in our Americas region.