Re: Help! Case Insensitivity with SELECT..WHERE..LIKE queries
In article <3405DC80.DE1429BA@vest.com>, evest@vest.com says...
> Help, anybody:
>
> I'm trying to run a SELECT...WHERE...LIKE query on product names and
> descriptions. However, I don't know of a way to ensure that the search
> is case-insensitive, as the LIKE conditions are dependent on user
> input. I need to make sure that the program returns a positive search
> result when a user types in "malaysia" even if "Malaysia" is the actual
> entry in the database. (I'm using Informix OWS 7.2 with Netscape
> Enterprise
> and LiveWire).
>
> I'd humbly appreciate any assistance. Thanks.
>
> -- Nathan
> ..................................................
> Vest Technologies
> http://www.vest.com
> 350 Bay Street
> Suite 100-123
> San Francisco, CA 94133
> 415/986-VEST
>
>
>
The only thing you can do is to force the user input to upper or lower
case and do a select using a stored procedure on the column to change the
data in the column to either upper or lower case. By using a function
like this I believe that any index on the column is not used. The
following Stored procedure could be used to convert the contents of a
column to upper case.
CREATE PROCEDURE upper (str VARCHAR(255)) RETURNING VARCHAR(255);
DEFINE I INTEGER;
DEFINE l INTEGER;
DEFINE retstr VARCHAR(255);
IF str IS NULL THEN
RETURN NULL;
ELSE
LET l = LENGTH(str);
LET retstr = '';
FOR I = 1 TO l
IF str[1,1] BETWEEN "a" AND "z" THEN
IF str[1,1] = 'a' THEN
LET retstr = retstr || 'A';
ELIF str[1,1] = 'b' THEN
LET retstr = retstr || 'B';
ELIF str[1,1] = 'c' THEN
LET retstr = retstr || 'C';
ELIF str[1,1] = 'd' THEN
LET retstr = retstr || 'D';
ELIF str[1,1] = 'e' THEN
LET retstr = retstr || 'E';
ELIF str[1,1] = 'f' THEN
LET retstr = retstr || 'F';
ELIF str[1,1] = 'g' THEN
LET retstr = retstr || 'G';
ELIF str[1,1] = 'h' THEN
LET retstr = retstr || 'H';
ELIF str[1,1] = 'I' THEN
LET retstr = retstr || 'I';
ELIF str[1,1] = 'j' THEN
LET retstr = retstr || 'J';
ELIFstr[1,1] = 'k' THEN
LET retstr = retstr || 'K';
ELIF str[1,1] = 'l' THEN
LET retstr = retstr || 'L';
ELIF str[1,1] = 'm' THEN
LET retstr = retstr || 'M';
ELIF str[1,1] = 'n' THEN
LET retstr = retstr || 'N';
ELIF str[1,1] = 'o' THEN
LET retstr = retstr || 'O';
ELIF str[1,1] = 'p' THEN
LET retstr = retstr || 'P';
ELIF str[1,1] = 'q' THEN
LET retstr = retstr || 'Q';
ELIF str[1,1] = 'r' THEN
LET retstr = retstr || 'R';
ELIF str[1,1] = 's' THEN
LET retstr = retstr || 'S';
ELIF str[1,1] = 't' THEN
LET retstr = retstr || 'T';
ELIF str[1,1] = 'u' THEN
LET retstr = retstr || 'U';
ELIF str[1,1] = 'v' THEN
LET retstr = retstr || 'V';
ELIF str[1,1] = 'w' THEN
LET retstr = retstr || 'W';
ELIF str[1,1] = 'x' THEN
LET retstr = retstr || 'X';
ELIF str[1,1] = 'y' THEN
LET retstr = retstr || 'Y';
ELSE
LET retstr = retstr || 'Z';
END IF;
ELSE
LET retstr = retstr || str[1,1];
END IF;
LET str = str[2,255];
END FOR;
-- LET retstr = retstr[2,255];-- BUG: req'd to strip off leading blank
RETURN retstr;
END IF;
END PROCEDURE;
Michael J. Erickson