Re: Simple Question: Case sensitivity

On Sunday 10 December 2000 07:23, Abe wrote:
> 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

There is also a case insensitive regular expression operator (~*) but I don't
know if its faster than the two upper() function calls and it is
PostgreSQL-specific:

$sql = "select surname, firstname, title, company, worktel, ext, hometel,
mobile, email, emailtwo from employees where firstname ~* '{$criteria}'
or surname ~* '{$criteria}'";

See Bruce Momjian's book:
LIKE expressions:
http://www.postgresql.org/docs/aw_pgsql_book/node51.html
Regular expressions:
http://www.postgresql.org/docs/aw_pgsql_book/node52.html

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------