Matching against a field case in-sensitive.

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if it is

possible to do a search or somehow set the column in a relation to be case
insensitive (on search at least).

I would however like to store it case sensitive so I can display it as it
was stored.

PostgreSQL has string
functions<http://developer.postgresql.org/pgdocs/postgres/functions-string.html&gt;
,
you can use these in your where clause

Hope this helps.

Regards,

Serge Fonville

In response to James Dooley :

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if it is
possible to do a search or somehow set the column in a relation to be case
insensitive (on search at least).

I would however like to store it case sensitive so I can display it as it was
stored.

test=# select * from foo where t = 'foo';
i | t
---+-----
1 | foo
(1 row)

test=*# select * from foo where lower(t) = lower('foo');
i | t
---+-----
1 | foo
2 | FOO
(2 rows)

You can also create an index based on lower() for such queries.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

KEYWORDS:
Identifier and key word names are case insensitive. Therefore
UPDATE MY_TABLE SET A = 5;
can equivalently be written as
uPDaTE my_TabLE SeT a = 5;

IDENTIFIERS:
Quoting an identifier also makes it case-sensitive,
whereas
unquoted names are always folded to lower case.
For example, the
identifiers FOO, foo, and
"foo" are considered the same by
PostgreSQL,
but
"Foo" and "FOO" are
different from these three and each other.
(The folding of
unquoted names to lower case in PostgreSQL is
incompatible with the SQL standard, which says that unquoted names
should be folded to upper case. Thus, foo
should be equivalent to "FOO" not
"foo" according to the standard. If you want
to write portable applications you are advised to always quote a
particular name or never quote it.)

in summary taking the quotes off identifiers will make your SQL statement case insensitive..

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html

HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

Date: Mon, 2 Mar 2009 15:40:27 +0100
Subject: [GENERAL] Matching against a field case in-sensitive.
From: jamdooley@gmail.com
To: pgsql-general@postgresql.org

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if it is possible to do a search or somehow set the column in a relation to be case insensitive (on search at least).

I would however like to store it case sensitive so I can display it as it was stored.

Ps. I am not interested in creating another column.

_________________________________________________________________
Windows Live™: Life without walls.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_032009

James Dooley wrote:

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if
it is possible to do a search or somehow set the column in a relation to
be case insensitive (on search at least).

I would however like to store it case sensitive so I can display it as
it was stored.

Ps. I am not interested in creating another column.

select value from table where relation ilike 'search-criteria'