Product Documentation

SQL Operations Guide

Previous Topic

Next Topic

Case Insensitive Search Options for FairCom DB SQL LONG Field Types

A limitation ofFairCom DB SQL LVARCHAR and LVARBINARY fields is the inability to use SQL functions such as UPPER() or LOWER() to perform case insensitive searches. To address this disadvantage of these field types, a proprietary FairCom DB SQL options clause is available to allow a case insensitive search. The ctoption(icontains) clause will allow a case insensitive search on a specific CONTAINS query. Include this clause on a query similar to the following:

SELECT * FROM mytable WHERE bigfield CONTAINS 'Search Phrase' ctoption(icontains)

The option is only valid for the current query. ctoption(icontains) can be used with both FairCom DB SQL LONG fields allowing both character and binary searching.