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(at)unc(dot)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(at)postgresql(dot)org
> >
>