caseless select
Is there a way, in Informix, I can do a caseless select? Something
similar to a grep -i on a Unix box? I'm trying to find names in a
database, but the users sometimes enter the query in caps or lower
case or some combination. I'd like to do something like the following:
select * from customers where last_name like "%$input%";
But, if last_name has Johnson, JOHNSON or johnson as values I could
have $input = "john" and all three last_name values would be returned.
Is this possible?
Thank You,
Nick Heesters
In version 7.3, you can do
select * from customers where UPPER(last_name like "%foo%");
IIUG also has stored procedures to (somewhat slowly) emulate this
Vic
Nicholas Pieter Heesters Jr. wrote:
> Is there a way, in Informix, I can do a caseless select? Something
> similar to a grep -i on a Unix box? I'm trying to find names in a
> database, but the users sometimes enter the query in caps or lower
> case or some combination. I'd like to do something like the following:
>
> select * from customers where last_name like "%$input%";>
> But, if last_name has Johnson, JOHNSON or johnson as values I could
> have $input = "john" and all three last_name values would be returned.
> Is this possible?
>
> Thank You,
> Nick Heesters
If you have 7.3x or later you can use the upper or lower function and
store names as all lower case or upper case, thus:
select * from customers where last_name_upper = UPPER( :input );
This will even use an index on last_name_upper. If you do not want to
store the upper case version of the name you can use upper on both the
input and the column but it will not use indexes:
select * from customers where UPPER( last_name ) = UPPER( "input );
If you do not have 7.30+ there is a stored procedure version of UPPER() in
the IIUG Repository but it is SLOW or you could store upper and convert
the input to upper case in code.
Art S. Kagel
"Nicholas Pieter Heesters Jr." wrote:
>
> Is there a way, in Informix, I can do a caseless select? Something
> similar to a grep -i on a Unix box? I'm trying to find names in a
> database, but the users sometimes enter the query in caps or lower
> case or some combination. I'd like to do something like the following:
>
> select * from customers where last_name like "%$input%";>
> But, if last_name has Johnson, JOHNSON or johnson as values I could
> have $input = "john" and all three last_name values would be returned.
> Is this possible?
>
> Thank You,
> Nick Heesters
"Nicholas Pieter Heesters Jr." wrote:
> Is there a way, in Informix, I can do a caseless select? Something
> similar to a grep -i on a Unix box? I'm trying to find names in a
> database, but the users sometimes enter the query in caps or lower
> case or some combination. I'd like to do something like the following:
>
> select * from customers where last_name like "%$input%";>
> But, if last_name has Johnson, JOHNSON or johnson as values I could
> have $input = "john" and all three last_name values would be returned.
> Is this possible?
>
> Thank You,
> Nick Heesters
Use either:
select * from customers where upper(last_name) like upper("%$input%")
Or
select * from customers where lower(last_name) like lower("%$input%")
There maybe a degradation in performance.
--
Compliments of QueriX
--------------------------------------------------------------------------------------------------
QueriX 4GL Compilers are Informix 4GL Compatible. Some features are:
True Windows GUI Clients. ActiveX support. HTML Report Generation.
More rigorous error handling. Connection to other RDBMS such as Oracle.
Connection to all versions of Informix 4GL with no need to change compiler.
For more details visit: http://www.querix.com/
---------------------------------------------------------------------------------------------------