Firebird Configuration - Disable Case Sensitivity

Question

I want to do a case insensitive search in the Firebird database without changing actual queries. In other words, I would like all my existing SELECT / WHERE / LIKE statements to extract BOB, Bob, and bob. Firebird configuration allows you to change this behavior?

Answer 1

In the end, I went with the creation of shadow columns containing lower versions of the required fields.

Answer 2

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

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

, / . , , .

, , , "Presley", "presley", "PRESLEY" "PrESley", .

, , , . , .

, , .

, . , /, , . , , , .

, (CI) / (AI). ( 2006 ) Firebird 2.0 AI/CI. , .

( , , . DE_DE , / ISO8859_1.)

UPPER(), Firebird, . DDL :

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

UPPER():

SELECT UPPER (LAST_NAME COLLATE DE_DE) FROM PERSONS;

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

lower()

LOWER()

: DSQL, ESQL, PSQL

: 2.0

: . , ASCII, () . , : , ASCII NONE, ASCII; OCTETS, .

: VAR(CHAR)

:

LOWER (str)

!

LOWER ​​ , . , DROP ALTER (UDF).

:

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

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

Answer 3

Necromancing.

Instead of a string field, you can specify sorting:

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

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

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