Cinchy Platform Documentation
Cinchy v5.0 - v5.5
Cinchy v5.0 - v5.5
  • Data Collaboration Overview
  • Other Wiki Spaces
    • Cinchy Data Sync
    • Angular SDK
    • JavaScript SQK
  • Release Notes
    • Release Notes
      • 5.0 Release Notes
      • 5.1 Release Notes
      • 5.2 Release Notes
      • 5.3 Release Notes
      • 5.4 Release Notes
      • 5.5 Release Notes
      • 5.6 Release Notes
  • Getting Help
  • Frequently Asked Questions
  • Deployment Guide
    • Deployment Installation Guides
      • Deployment Planning Overview and Checklist
        • Deployment Architecture Overview
          • Kubernetes Deployment Architecture
          • IIS Deployment Architecture
        • Deployment Prerequisites
          • Single Sign-On (SSO) Integration
            • Enabling TLS 1.2
            • Configuring ADFS
            • AD Group Integration
      • Kubernetes Deployment Installation
        • Disabling your Kubernetes Applications
        • Changing your File Storage Configuration
        • Using Self-Signed SSL Certs (Kubernetes Deployments)
        • Deploying the CLI (Kubernetes)
      • IIS Deployment Platform Installation
        • Deploying Connections and the CLI (IIS)
        • Deploying the Event Listener/Worker (IIS)
    • Upgrade Guides
      • Upgrading Cinchy Versions
        • Cinchy Upgrade Utility
        • Kubernetes Upgrades
          • v5.1 (Kubernetes)
          • v5.2 (Kubernetes)
          • v5.3 (Kubernetes)
          • v5.4 (Kubernetes)
          • v5.5 (Kubernetes)
          • v5.6 (Kubernetes)
          • Updating the Kubernetes Image Registry
          • Upgrading AWS EKS Kubernetes Version
          • Upgrading AKS (Azure Kubernetes Service)
        • IIS Upgrades
          • v4.21 (IIS)
          • v4.x to v5.x (IIS)
          • v5.1 (IIS)
          • v5.2 (IIS)
          • v5.3 (IIS)
          • v5.4 (IIS)
          • v5.5 (IIS)
          • v5.6 (IIS)
      • Upgrading from v4 to v5
  • Guides for Using Cinchy
    • User Guides
      • Overview of the Data Browser
      • The Admin Panel
      • User Preferences
        • Personal Access Tokens
      • Table Features
      • Data Management
      • Queries
      • Version Management
        • Versioning Best Practices
      • Commentary
    • Builder Guides
      • Best Practices
      • Creating Tables
        • Attaching Files
        • Columns
        • Data Controls
          • Data Entitlements
          • Data Erasure
          • Data Compression
        • Restoring Tables, Columns, and Rows
        • Formatting Rules
        • Indexing and Partitioning
        • Linking Data
        • Table and Column GUIDs
        • System Tables
      • Saved Queries
      • CinchyDXD Utility
        • Building the Data Experience (CinchyDXD)
        • Packaging the Data Experience (CinchyDXD)
        • Installing the Data Experience (CinchyDXD)
        • Updating the Data Experience (CinchyDXD)
        • Repackaging the Data Experience (CinchyDXD)
        • Reinstalling the Data Experience (CinchyDXD)
      • Multi-Lingual Support
      • Integration Guides
    • Administrator Guide
    • Additional Guides
      • Monitoring and Logging on Kubernetes
        • Grafana
        • Opensearch Dashboards
          • Setting up Alerts
        • Monitoring via ArgoCD
      • Maintenance
      • GraphQL (Beta)
      • System Properties
      • Enable Data At Rest Encryption
      • MDQE
      • Application Experiences
        • Network Map
          • Custom Node Results
          • Custom Results in the Network Map
        • Setting Up Experiences
  • API Guide
    • API Overview
      • API Authentication
      • API Saved Queries
      • ExecuteCQL
      • Webhook Ingestion
  • CQL
    • The Basics of CQL
      • CQL Examples
      • CQL Functions Master List
      • CQL Statements Overview
        • Cinchy DML Statements
        • Cinchy DDL Statements
      • Cinchy Supported Functions
        • Cinchy Functions
        • Cinchy System Values
        • Cinchy User Defined Functions
          • Table-Valued Functions
          • Scalar-Valued Functions
        • Conversion Functions
        • Date and Time Types and Functions
          • Return System Date and Time Values
          • Return Date and Time Parts
          • Return Date and Time Values From Their Parts
          • Return Date and Time Difference Values
          • Modify Date and Time Values
          • Validate Date and Time Values
        • Logical Functions
        • Mathematical Functions
        • String Functions
        • Geometry and Geography Data Type and Functions
          • OGC Methods on Geometry & Geography Instances
          • Extended Methods on Geometry & Geography Instances
        • Full Text Search Functions
        • Connections Functions
        • JSON Functions
  • Meta Forms
    • Introduction to Meta-Forms
    • Meta-Forms Deployment Installation Guide
      • Deploying Meta-Forms (Kubernetes)
      • Deploying Meta-Forms (IIS)
    • Creating a Dynamic Meta-Form (Using Tables)
    • Creating a Dynamic Meta-Form Example (Using Form Designer)
    • Forms Data Types
    • Adding Links to a Form
    • Rich Text Editing in Forms
Powered by GitBook
On this page
  • Overview
  • 1. CONTAINS
  • 2. CONTAINS Modifiers
  • 2.1 Prefix Term
  • 2.2 Generation Term
  • 2.3 Proximity Term
  • 2.4 Boolean Operators
  • 2.5 NEAR
  • 3. FREETEXT

Was this helpful?

Export as PDF
  1. CQL
  2. The Basics of CQL
  3. Cinchy Supported Functions

Full Text Search Functions

PreviousExtended Methods on Geometry & Geography InstancesNextConnections Functions

Last updated 2 years ago

Was this helpful?

Overview

Full Text Searching can provide robust search capabilities on columns that have a Full Text Index. You are able to use the predicates CONTAINS and FREETEXT for this purpose.

In order to perform a Full Text Search,

Full Text Searching is currently only available for those using SQL Server 2016 or up.

1. CONTAINS

CONTAINS is a predicate used in the WHERE clause of a CQL SELECT statement to perform full-text search on full-text indexed columns containing character-based data types.

CONTAINS can search for:

  • A word or phrase.

  • The prefix of a word or phrase.

  • A word near another word.

  • A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

General Syntax

Using this general syntax without any modifiers, your results will return the specific rows that match the exact word or phrase specified between the single quotes in line 2.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = ''  
SELECT [column]   
FROM [domain].[table]
WHERE CONTAINS([Title], @SearchWord)

Example Syntax

In the following example, we want to return all rows from the Cinchy Wiki Documentation table (in the Product domain) that match the exact word overview in their Title column.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'overview'  
SELECT [Title]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([Title], @SearchWord)

Example Results

2. CONTAINS Modifiers

There are many modifiers you can add to your CONTAINS full text search query to receive more specific results.

2.1 Prefix Term

Using a prefix term modifier will return results with your specified prefix. For example, the prefix 'over' could return overview, overture, overruled, etc.

To use a prefix term, wrap your term in single, then double quotes and put an asterix at the end: '"example*"'

General Syntax:

DECLARE @SearchWord VARCHAR(30)
SET @SearchWord = '"*"'
SELECT [column]   
FROM [domain].[table]
WHERE CONTAINS([column], '"*"')

Example Syntax

In this example, we have modified our search so that we receive all results where the any word in the Title column contains the prefix 'over'.

DECLARE @SearchWord VARCHAR(30)
SET @SearchWord = '"over*"'
SELECT [Title]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([Title], '"over*"')

Example Results

2.2 Generation Term

A generation term modifier searches for all the different tenses and conjugations of a verb or both the singular and plural forms of a noun (an inflectional search) or for synonymous forms of a specific word (a thesaurus search).

General Syntax (Inflectional)

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = ''  
SELECT [Column]   
FROM [domain].[table]
WHERE CONTAINS([column], 'FORMSOF(INFLECTIONAL, "")') 

Example Syntax (Inflectional)

In this example, our query will return all results with different tense and conjugations of our search word, 'gave'.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'gave'  
SELECT [Summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([Summary], 'FORMSOF(INFLECTIONAL, "gave")') 

Example Results (Inflectional)

General Syntax (Thesaurus)

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = ''  
SELECT [Column]   
FROM [domain].[table]
WHERE CONTAINS([column], 'FORMSOF(THESAURUS, "")') 

Example Syntax (Thesaurus)

In this example, we want to return all results where the data in the Summary column matches the meaning of our search term.

I.E. "install" might return results with "deploy", "configure", "set", etc.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'install'  
SELECT [Summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([Summary], 'FORMSOF(THESAURUS, "install")') 

2.3 Proximity Term

A proximity term will return words or phrases that are near to each other. You can also specify the maximum number of non-search terms that separate the first and last search terms.

Note that proximity terms in Cinchy do not adhere to the specified order written in the query. You will receive results of both "term 1+term 2" as well as "term 2+term 1".

General Syntax

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'NEAR((term1, term2), #)'
SELECT [column]   
FROM [domain].[table]
WHERE CONTAINS([column], 'NEAR((term1, term2), #)')

Example Syntax

This example returns all results where the terms "first" and "page" appear within two words of each other.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'NEAR((first, page), 2)'
SELECT [summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([summary], 'NEAR((first, page), 2)')

Example Results

2.4 Boolean Operators

With CONTAINS, you can use AND, OR, and AND NOT to specify your results.

General Syntax

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'term operator term'  
SELECT [column]   
FROM [domain].[table]
WHERE CONTAINS([column], @SearchWord)

Example 1 (AND) Syntax

In this example, we want search results from the Summary column of the Cinchy Wiki Documentation page that contain both the word 'user' and the word 'page'.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'user AND page'  
SELECT [summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([summary], @SearchWord)

Example 1 (AND) Result

Example 2 (OR) Syntax

In this example, we want search results from the Summary column of the Cinchy Wiki Documentation page that contain either the word 'user' or the word 'page'.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'user OR page'  
SELECT [summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([summary], @SearchWord)

Example 2 (OR) Results

Example 3 (AND NOT) Syntax

In this example, we want search results from the Summary column of the Cinchy Wiki Documentation page that contain the word 'user' and not the word 'page'.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'user AND NOT page'  
SELECT [summary]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([summary], @SearchWord)

Example 3 (AND NOT) Results

2.5 NEAR

You can use this modifier to return results with terms that appear near each other (i.e. within the same data cell).

General Syntax

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'term1 NEAR term2'  
SELECT [column]   
FROM [domain].[table]
WHERE CONTAINS([title], @SearchWord)

Example Syntax

In this example, we want to return all results where the data in the Title column has the term "data" appearing near the term "CinchyDXD".

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord = 'Data NEAR CinchyDXD'  
SELECT [title]   
FROM [Product].[Cinchy Wiki Documentation]
WHERE CONTAINS([title], @SearchWord)

Example Results

3. FREETEXT

The FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string.

At a high level, this commands finds matches based on separating the string into individual words, determining inflectional versions of the word and using a thesaurus to expand or replace the term to improve the search.

The difference between FREETEXT and CONTAINS is that it searches for the values that match the meaning of a phrase and not just exact words. It is therefore a better option if you are searching phrases, in lieu of individual words.

General Syntax

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord =''  
SELECT [column]
FROM [domain].[table]
WHERE FREETEXT([summary], @SearchWord);

Example Syntax

In this example, we want to return all results where the data in the Summary column matches the meaning of our search phrase.

I.E. "installation guide" might return results with "deployment instructions", "set up guide", etc.

DECLARE @SearchWord VARCHAR(30)  
SET @SearchWord ='installation guide'  
SELECT [summary]
FROM [Product].[Cinchy Wiki Documentation]
WHERE FREETEXT([summary], @SearchWord);

In order to return the synonymous forms, you must have a Thesaurus file configured.

In order to use FREETEXT, you must have a Thesaurus file configured.

each column in your query will need a Full Text Index.
Find more information here.
Find more information here.