Frank Millman wrote:
Frank Millman wrote:
Hi all
Is there an LC_COLLATE setting, or any other method, which allows all
data in a database to be treated in a case-insensitive manner?
I was hoping to stimulate some discussion on this topic, but it seems I will
have to kick-start it myself and see if anyone responds.
My area of interest is general accounting/business systems, using a typical
Western character set. I would imagine that this is a common scenario, but
it is not universal, so read my comments in this context.
In the good old days, data entry was always done in upper case, by dedicated
data entry personnel. These days, it is typically done by a wide variety of
individuals, who carry out a wide range of tasks, most of which require
lower case (word processing, email) with the occasional use of the shift key
to enter a capital letter.
In this context, here are two undesirable effects.
Martijn has pointed to a case-insensitive type, but I'll add a couple of
points.
1. The user tries to call up account 'A001', but they enter 'a001'. First
problem, the system does not find the account. Second problem, the system
allows them to create a new account with the code 'a001'. Now you have
'A001' and 'a001'. This is a recipe for chaos.
The basic problem here is that the value isn't text. This is partly the
fault of development-systems not having a way to deal with sophisticated
types in databases.
What should happen is that you define some suitable type "AccountCode"
which is defined as allowing character data in the form of (e.g.)
"[A-Z][0-9][0-9][0-9]". That type can cast to/from text but doesn't need
access to the full range of text-handling functions (e.g. concatenating
two account-codes is probably meaningless). Of course, you want to
define this in one place and have both the database constraints and
user-interface understand what you want.
Ironically, MS-Access does this quite well with its tight coupling of
user-interface and data storage.
2. The user tries to call up a product item using a search string on the
description. Assume they enter 'Wheel nut'. Assume further that the person
who created the product item used the description 'Wheel Nut'. Try
explaining to the user why the system cannot find the item they are looking
for.
Here, I'm not convinced a case-insensitive type is any more useful than
just using ILIKE.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
