Firebird configuration - turn case-sensitivity off

I'm looking to perform case-inse开发者_C百科nsitive search in a Firebird database, without modifying actual queries. In other words, I'd like all my existing "SELECT/WHERE/LIKE" statements to retrieve BOB, Bob, and bob. Does Firebird configuration allow to modify this behavior?

Try using something like: Imagine you have a table of persons like this one:

CREATE TABLE PERSONS (
  PERS_ID INTEGER NOT NULL PRIMARY KEY,
  LAST_NAME VARCHAR(50),
  FIRST_NAME VARCHAR(50)
);

Now there is an application, which allows the user to search for persons by last name and/or first name. So the user inputs the last name of the person he is searching for.

We want this search to be case insensitive, i.e. no matter if the user enters "Presley", "presley", "PRESLEY", or even "PrESley", we always want to find the King.

Ah yes, and we want that search to be fast, please. So there must be an index speeding it up.

A simple way to do case insensitive comparisons is to uppercase both strings and then compare the uppercased versions of both strings.

Uppercasing has limitations, because some letters cannot be uppercased. Note also that there are languages/scripts where there is no such thing as case. So the technique described in this article will work best for European languages.

In order to get really perfect results one would need a case insensitive (CI) and/or accent insensitive (AI) collation. However, at the time of this writing (July 2006) there are only two Czech AI/CI collations for Firebird 2.0. The situation will hopefully improve over time.

(You should know the concepts of Character Sets and Collations in order to understand what comes next. I use the DE_DE collation in my examples, this is the collation for German/Germany in the ISO8859_1 character set.)

In order to get correct results from the UPPER() function that is built into Firebird, you must specify a collation. This can be in the DDL definition of the table:

CREATE TABLE PERSONS (
  PERS_ID INTEGER NOT NULL PRIMARY KEY,
  LAST_NAME VARCHAR(50) COLLATE DE_DE,
  FIRST_NAME VARCHAR(50) COLLATE DE_DE
);

or it can be done when calling the UPPER() function:

SELECT UPPER (LAST_NAME COLLATE DE_DE) FROM PERSONS;

http://www.destructor.de/firebird/caseinsensitivesearch.htm

or you can edit your queries and add the lower() function

LOWER()

Available in: DSQL, ESQL, PSQL

Added in: 2.0

Description: Returns the lower-case equivalent of the input string. This function also correctly lowercases non-ASCII characters, even if the default (binary) collation is used. The character set must be appropriate though: with ASCII or NONE for instance, only ASCII characters are lowercased; with OCTETS, the entire string is returned unchanged.

Result type: VAR(CHAR)

Syntax:

LOWER (str)

Important

If the external function LOWER is declared in your database, it will obfuscate the internal function. To make the internal function available, DROP or ALTER the external function (UDF).

Example:

select field from table
  where lower(Name) = 'bob'

http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-lower.html

Necromancing.
Instead of a lowercase-field, you can specify the collation:

SELECT field FROM table
WHERE Name = 'bob' COLLATE UNICODE_CI

See https://firebirdsql.org/refdocs/langrefupd21-collations.html

Maybe you can also specify it as the default character-set for the database, see http://www.destructor.de/firebird/charsets.htm

Eventually I went with creating shadow columns containing lower-cased versions of required fields.