Re: Case sensitivity and the LIKE clause

Just convert the case of the column you're examining to the case of the constraint, like this:

SELECT * FROM tblProd WHERE upper(ProdName) LIKE '%CHI%'; SELECT * FROM tblProd WHERE upper(ProdName) LIKE upper('%cHi%');

or this:

SELECT * FROM tblProd WHERE lower(ProdName) LIKE '%chi%'; SELECT * FROM tblProd WHERE lower(ProdName) LIKE lower('%cHi%');

Of course, this will prevent the optimizer from using any index on ProdName which may exist, but in this query it wouldn't have used an index anyway, since the constraint began with a '%'.

In article <3741CEFC.8CC97FC9_at_onlinetech.net>,   Brett Fattori <brettf_at_onlinetech.net> wrote:
> How can I get a statement like this to return all possible records:
>
> SELECT * FROM tblProd WHERE ProdName LIKE '%chi%';
>
> When the table might contain data like this:
>
> ProdName
> --------
> Chicken fajitas
> Bold CHICKEN Ranch Dip
> Tasty chicken burgers
>
> All I get now is the "Tasty chicken burgers" record. Is there such a
> thing as case insensitivity and how do I use it/not use it?
>
> --
> Brett Fattori
>
> Visit The Render Engine
> -----------------------
> http://www.renderengine.com
> "truely timely, truely resourceful"
> Your resource for everything trueSpace!
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 18 1999 - 19:47:01 CDT