Re: Case Sensitivity in select

Stu schrieb:

> Is it possible to set a parameter which will make a select statement
> case insensitive. i.e. so that select * from words where word='hello'
> will bring back records where word='HELLO' or 'Hello'.

You can do the following

select * from words where lower(word) = 'hello'

or

select * from words where lower(word) = lower('Hello')

But I think that you will always have full scans because you are using functions.

If this is the only column you will search case insensitive, you can add a new
column to this table, which will be filled by a trigger with lower case versions of this word.
Then you can create a non-unique index on that column.

Now statements like

select * from words where ci_word = lower('Hello')

use the proper index.

I hope this will help you, let me know.

Regards, Stephan

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------