Searching string with mixed uppercase and lowercase
Hi, Informix Guru's.
Anybody knows how to construct a select what ignore the upper/lowercase of
a string ?
Examples:
SELECT Name
FROM Table
WHERE Name = "John"
OR
SELECT Name
FROM Table
WHERE Name = "JOhN"
OR
SELECT Name
FROM Table
WHERE Name = "jOhN"
etc...
Thanks in advance.
Assuming the name is a literal that you know, you could do:
SELECT Name
FROM Table
WHERE Name matches "[Jj][Oo][Hh][Nn]"
Joan Carles Cladellas wrote:
> Hi, Informix Guru's.
>
> Anybody knows how to construct a select what ignore the upper/lowercase of
> a string ?
>
> Examples:
>
> SELECT Name
> FROM Table
> WHERE Name = "John">
> OR
>
> SELECT Name
> FROM Table
> WHERE Name = "JOhN">
> OR
>
> SELECT Name
> FROM Table
> WHERE Name = "jOhN">
> etc...
>
> Thanks in advance.
A clean and well-engineered design upshifts the alphabetic keys permanently in
the database, and then upshifts the user input string before doing the select.
If you absolutely have to preserve case in the database (often not the case),
you can maintain a redundant non-searched field, or pass the upshifted database
strings through a mixed case algorithm before displaying them.
Joan Carles Cladellas wrote:
> Hi, Informix Guru's.
>
> Anybody knows how to construct a select what ignore the upper/lowercase of
> a string ?
>
> Examples:
>
> SELECT Name
> FROM Table
> WHERE Name = "John">
> OR
>
> SELECT Name
> FROM Table
> WHERE Name = "JOhN">
> OR
>
> SELECT Name
> FROM Table
> WHERE Name = "jOhN">
> etc...
>
> Thanks in advance.
--
Richard C. Auslander
Database Manager
AirFlash, Inc.
1733 Woodside Rd., Suite #110
Redwood City, CA 94061
(650) 556-7928
www.airflash.com