Received: from mail.ldssingles.com (mail.ldssingles.org [63.226.117.101]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4THDcE01470 for ; Tue, 29 May 2001 13:13:38 -0400 (EDT) (envelope-from mark@zserve.com) Received: from mark.ldssingles.com (mark.ldssingles.com [192.168.1.31]) by mail.ldssingles.com (8.11.2/8.11.2) with ESMTP id f4THD3W09051; Tue, 29 May 2001 11:13:03 -0600 Message-Id: <200105291713.f4THD3W09051@mail.ldssingles.com> Subject: Re: Case Insensitive Queries From: Mark To: ANDREW PERRIN Cc: pgsql-sql@postgresql.org In-Reply-To: Content-Type: text/plain X-Mailer: Evolution (0.9 - Preview Release) Date: 29 May 2001 10:21:15 -0600 Mime-Version: 1.0 X-Archive-Number: 200105/313 X-Sequence-Number: 3154 We tried these but it didn't work. However, that's because username is a bpchar and not a varchar, so its padded with blanks. so we tried where lower(trim(username)) = 'test' and it works. We'll change that column to varchar. The real problem was in the datatype for username. Thanks, On 29 May 2001 12:35:53 -0400, ANDREW PERRIN wrote: > Try: > > - The ILIKE operator, for example, > > SELECT * FROM account WHERE username ILIKE "test"; > > - upper() or lower(), for example, > > SELECT * FROM accont WHERE lower(username) = "test"; > > --------------------------------------------------------- > Andrew J. Perrin - Assistant Professor of Sociology > University of North Carolina, Chapel Hill > 269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA > andrew_perrin@unc.edu - http://www.unc.edu/~aperrin > > On 29 May 2001, Mark wrote: > > > 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. > > > > grep'd the source, but stricmp is only used for keywords and not actual > > column data. > > > > Any help would be greatly appreciated. > > > > Thanks, > > > > Mark > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >