Thread: Case Insensitive Queries

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

select * from account where lower(username) = lower('test');


Troy




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

Mark writes:
> Is it possible to execute a query using a where clause that allows case
> insensitive comparison between a field and text.

select * from account where upper(username) = upper('test')

(Upper used because, as has been remarked on this list and in other places,
folding from richer character sets is likely to get better matches this way).

And yes, you can create an index on upper(fieldname).

Dan

> 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

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