Re: Case Insensitive Queries

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
> > 
>