Received: from mailsys01.intnet.net (antares.intnet.net [198.252.32.143]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4TGsfE92566 for ; Tue, 29 May 2001 12:54:41 -0400 (EDT) (envelope-from jconway2@home.com) Received: from [206.19.64.3] (HELO jecw2k1) by mailsys01.intnet.net (CommuniGate Pro SMTP 3.3.2) with SMTP id 8886054; Tue, 29 May 2001 12:53:46 -0400 Message-ID: <005701c0e860$0d3d9ce0$dad410ac@jecw2k1> From: "Joe Conway" To: "Mark" , References: <200105291603.f4TG3BW08512@mail.ldssingles.com> Subject: Re: Case Insensitive Queries Date: Tue, 29 May 2001 09:54:39 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200105/312 X-Sequence-Number: 3153 > Is it possible to execute a query using a where clause that allows case > insensitive comparison between a field and text. > > For example: > > select * from account where username = 'test' > > where username could be 'Test', which would be a match. As is, this > compare is case sensitive. > You can use ~* (see 4.5.2. POSIX Regular Expressions http://www.postgresql.org/idocs/index.php?functions-matching.html ) or force everything to either upper or lower using the same named functions (see 4.4. String Functions and Operators http://www.postgresql.org/idocs/index.php?functions-string.html ). You can build an index on upper(username) or lower(username), so that may be a good choice if you don't really need a regular expression. Hope this helps, -- Joe