On 4 Jul 2015, at 5:46pm, William Drago <wdrago at suffolk.lib.ny.us> wrote:
> Clearly, in this case, using COLLATE NOCASE in the table definition is the > right thing to do. Under what conditions would using it in the index instead > be the right > thing to do? It's rare. Sometimes you have a column where case normally does matter, but occasionally want to search case-insensitive. This might happen in a field where acronyms and initialisms are used a lot and you need to distinguish between them. For example, an English Language dataset might normally need to distinguish between 'ACID' and 'acid' but you might want to enable a fast searching facility, without your users having to be fussy about typing capital letters. It's also possible to do it the other way: define the column as COLLATE NOCASE but have an index include the column COLLATE BINARY. Or even COLLATE RTRIM. Simon.
