skip to main content
Querying with OData Version 4 : Formulating queries with OData Version 4 : Searching text-based columns
  

Try Now

Searching text-based columns

Different data store types support different levels of indexing and searching. Indexing increases the efficiency of searches in tables with many records. Querying to find particular values can be expensive when the search must span many columns and many records. To improve performance, you can restrict searches to particular text-based columns by using the search functionality in your queries. For OData version 4, searches are executed using the $search system query option. To search across all columns in the schema, even those not enabled in the schema map for searching, you can use OData $filter.
This release supports use of $search for all data store types, and full-text search taking advantages of indexes in the following data source types:
*DB2 on Linux, UNIX, and Windows — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the DB2 documentation for more information.
*Oracle — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the Oracle documentation for more information.
*Microsoft SQL Server — Each column to be searched must have a separate full text index and the full text index engine must be running. See the Microsoft documentation for more information.
To use text search with OData version 4:
1. For data stores that support full-text search, make sure that the underlying data store is indexed and is up to date with the current schema.
2. Enable search for the indexed columns in the Hybrid Data Pipeline data source schema map, as described in Configuring data sources for OData Version 4 connectivity and selecting Full Text as the search type.
3. Use the $search query option with a search string. For details, refer to the OData Version 4.0 Specification.
Hybrid Data Pipeline treats multiple terms by using a logical and. For example, a search for Sales & Marketing returns records that contain both the word Sales and the word Marketing, the ampersand is ignored. The case-sensitivity of the search string depends on the underlying data source.
Note: The hash (#) character is not allowed in a search expression. To use the hash character in a search expression, it will need to be percent encoded.