Thread: Case in-sensitive searches

Is there a paramater to a select statement that one can issue to make the
search case insesitive?  Thanks

Michael

Hi,

I found an inellegant sollution. You can put every character in uppercase
like that :

    Select * From table_membres where upper(nom) like upper('myname');

JK

Mike Lemler wrote:

> Is there a paramater to a select statement that one can issue to make the
> search case insesitive?  Thanks
>
> Michael

upper()/lower() are defined only for text fields. How to deal with charN ?

Thanx for the attention!

    Marin


          -= Why do we need gates in a world without fences? =-



On Tue, 19 May 1998, Jerome Knobl wrote:

> Hi,
>
> I found an inellegant sollution. You can put every character in uppercase
> like that :
>
>     Select * From table_membres where upper(nom) like upper('myname');
>

On Tue, 19 May 1998, Marin D wrote:

>
> upper()/lower() are defined only for text fields. How to deal with charN ?
>
version 6.3:

prova=> create table a ( var varchar(20), ch char(30));
CREATE
prova=> insert into a values ('nome','pippo');
INSERT 76394 1
prova=> select * from a;
var |ch
----+------------------------------
nome|pippo
(1 row)

prova=> select upper(var), upper(ch) from a;
upper|upper
-----+------------------------------
NOME |PIPPO
(1 row)
                                              Jose'

Jose' Soares Da Silva wrote:
>
> On Tue, 19 May 1998, Marin D wrote:
>
> >
> > upper()/lower() are defined only for text fields. How to deal with charN ?
> >
> version 6.3:
>
> prova=> create table a ( var varchar(20), ch char(30));
> CREATE
> prova=> insert into a values ('nome','pippo');
> INSERT 76394 1
> prova=> select * from a;
> var |ch
> ----+------------------------------
> nome|pippo
> (1 row)
>
> prova=> select upper(var), upper(ch) from a;
> upper|upper
> -----+------------------------------
> NOME |PIPPO
> (1 row)
>                                               Jose'


From http://www.postgresql.org/docs/user/c05.htm :

Operator  Description                        Usage

~*        Match (regex), case insensitive    'thomas' ~* '*.Thomas*.'


Ex:

Select * From table_membres where nom ~* '.*myname.*';


-Tony

--
----------C-Y-B-E-R-S-O-L-U-T-I-O-N-S----------------
Anton Stöckl                    mailto:tony@cys.de
CyberSolutions GmbH             http://www.cys.de
Frankfurter Ring 193A           Phone +49 89 32369223
80807 Muenchen                  Fax   +49 89 32369220
------W-E----M-A-K-E----I-T----P-O-S-S-I-B-L-E-------

Jerome Knobl wrote:
>
> Hi,
>
> I found an inellegant sollution. You can put every character in uppercase
> like that :
>
>     Select * From table_membres where upper(nom) like upper('myname');
>

I use a much less elegant solution that works extremely well for me. I
add a copy of the column to the table and fold it uppercase before I
load the table. I also do some additional processing in it, in order to
eliminate things that may interfere with indexing, such as accents in
European characters. The application then decides whether to search in
the original or in the processed copy of the column, but the original
data are always returned to the user.

--Gene