Thread: RE: Simple Question: Case sensitivity
Abe: It's an SQL thing or a scripting thing. It's probably easiest and
safest in the SQL:
select firstname, surname from employees
where upper(firstname) like upper('%$criteria%') or
upper(surname) like upper('%$criteria%')
That is, force the column and the search string to uppercase befor
comparing, and it won't matter how it's stored in the database.
Cheers!
--
David Hancock
-----Original Message-----
From: Abe
To: pgsql-general@postgresql.org
Sent: 12/10/00 7:23 AM
Subject: [GENERAL] Simple Question: Case sensitivity
This is probably an easy question for most but here goes:
I am using PHP3 and postgres 6.5
I am trying to do a search on a peoples database and it works fine
except
for the fact that I want to make it case insensitive as some in the
database
are Smith and some are jones. Is this a scripting thing or can it be
done
in my query.
Here is the query:
$sql = "select surname, firstname, title, company, worktel, ext,
hometel,
mobile, email, emailtwo from employees where firstname like
'%$criteria%' or
surname like '%$criteria%'";
Thanks in advance,
Abe
Thanks David,
works a treat!
Abe
----- Original Message -----
From: "Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com>
To: "'Abe '" <abe@fish.tm>; <pgsql-general@postgresql.org>
Sent: Sunday, December 10, 2000 12:48 PM
Subject: RE: [GENERAL] Simple Question: Case sensitivity
> Abe: It's an SQL thing or a scripting thing. It's probably easiest and
> safest in the SQL:
>
> select firstname, surname from employees
> where upper(firstname) like upper('%$criteria%') or
> upper(surname) like upper('%$criteria%')
>
> That is, force the column and the search string to uppercase befor
> comparing, and it won't matter how it's stored in the database.
>
> Cheers!
> --
> David Hancock
>
> -----Original Message-----
> From: Abe
> To: pgsql-general@postgresql.org
> Sent: 12/10/00 7:23 AM
> Subject: [GENERAL] Simple Question: Case sensitivity
>
> This is probably an easy question for most but here goes:
>
> I am using PHP3 and postgres 6.5
>
> I am trying to do a search on a peoples database and it works fine
> except
> for the fact that I want to make it case insensitive as some in the
> database
> are Smith and some are jones. Is this a scripting thing or can it be
> done
> in my query.
>
> Here is the query:
>
> $sql = "select surname, firstname, title, company, worktel, ext,
> hometel,
> mobile, email, emailtwo from employees where firstname like
> '%$criteria%' or
> surname like '%$criteria%'";
>
> Thanks in advance,
> Abe
>
"Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com> writes:
> Abe: It's an SQL thing or a scripting thing. It's probably easiest and
> safest in the SQL:
>
> select firstname, surname from employees
> where upper(firstname) like upper('%$criteria%') or
> upper(surname) like upper('%$criteria%')
>
> That is, force the column and the search string to uppercase befor
> comparing, and it won't matter how it's stored in the database.
Related to this, is there any way to make an index for a table
case-insensitive? If you have an index, but use upper() in the select,
the index is not used.
Tomas
On 11 Dec 2000, Tomas Berndtsson wrote:
> "Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com> writes:
>
> > Abe: It's an SQL thing or a scripting thing. It's probably easiest and
> > safest in the SQL:
> >
> > select firstname, surname from employees
> > where upper(firstname) like upper('%$criteria%') or
> > upper(surname) like upper('%$criteria%')
> >
> > That is, force the column and the search string to uppercase befor
> > comparing, and it won't matter how it's stored in the database.
>
> Related to this, is there any way to make an index for a table
> case-insensitive? If you have an index, but use upper() in the select,
> the index is not used.
You can create your index as upper or lower and it'll be used in a
select that uses upper().
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Tomas Berndtsson <tomas@nocrew.org> writes:
> Related to this, is there any way to make an index for a table
> case-insensitive? If you have an index, but use upper() in the select,
> the index is not used.
Sure, make a functional index:
play=> create table foo (f1 text);
CREATE
play=> create index fooi on foo (upper(f1));
CREATE
This index will be considered for queries like:
play=> explain select * from foo where upper(f1) = 'z';
NOTICE: QUERY PLAN:
Index Scan using fooi on foo (cost=0.00..8.16 rows=10 width=12)
EXPLAIN
play=> explain select * from foo where upper(f1) > 'a' and upper(f1) < 'z';
NOTICE: QUERY PLAN:
Index Scan using fooi on foo (cost=0.00..8.21 rows=10 width=12)
EXPLAIN
You can use the same sort of ploy for lower() or any other simple
function of the table's columns. Don't go overboard with a ton of
indexes though; remember each index costs time when updating the
table...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tomas Berndtsson <tomas@nocrew.org> writes:
> > Related to this, is there any way to make an index for a table
> > case-insensitive? If you have an index, but use upper() in the select,
> > the index is not used.
>
> Sure, make a functional index:
>
> play=> create table foo (f1 text);
> CREATE
> play=> create index fooi on foo (upper(f1));
> CREATE
Ah, great, thanks. Couldn't see anything about that in the manual.
> This index will be considered for queries like:
>
> play=> explain select * from foo where upper(f1) = 'z';
Don't you need upper('z')?
Tomas
Simply create a functional index:
CREATE INDEX foo ON employees (UPPER(firstname));
However, I have just tried this, and it doesn't work for some reason. Anybody?
Cheers...
MikeA
-----Original Message-----
From: Tomas Berndtsson [mailto:tomas@nocrew.org]
Sent: 11 December 2000 10:49
To: Hancock, David (DHANCOCK)
Cc: 'pgsql-general@postgresql.org '
Subject: Re: [GENERAL] Simple Question: Case sensitivity
"Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com> writes:
> Abe: It's an SQL thing or a scripting thing. It's probably easiest and
> safest in the SQL:
>
> select firstname, surname from employees
> where upper(firstname) like upper('%$criteria%') or
> upper(surname) like upper('%$criteria%')
>
> That is, force the column and the search string to uppercase befor
> comparing, and it won't matter how it's stored in the database.
Related to this, is there any way to make an index for a table
case-insensitive? If you have an index, but use upper() in the select,
the index is not used.
Tomas
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
Tomas Berndtsson <tomas@nocrew.org> writes:
>> This index will be considered for queries like:
>>
>> play=> explain select * from foo where upper(f1) = 'z';
> Don't you need upper('z')?
yup ... or at least 'Z' ... sloppy example :-(
regards, tom lane