Re: lower function in SQL
Leszek Buczkowski wrote:
>
> Hi,
>
> I write web-apps based on Netscape LiveWire and I am to deal with
> Informix now. It is ok but I find a problem - the following JavaScript
> expression with SQL query for case-insensitive searching...
>
> "select * from my_table where lower (name) = '" + searchtext.toLowerCase
> () + "'"
>
> ...works well in Oracle SQL but doesn't work in Informix. The problem is
> with "lower" function defined by Oracle but unknown for Informix. Is
> there any function that converts string to lower (or upper) case in
> Informix SQL? (There is no documentation about Informix SQL in Netscape
> SuitSpot).
>
> Leszek Buczkowski
> lb@ikp.pl
I found the solution - I've got it from:
-------------------------------------------
http://www.iiug.org/techinfo/faq/ifaq05.htm
-------------------------------------------
5.11 Is there *really* no "Upper" function!?
One of the largest omissions in Informix's SQL is the lack of an "UPPER"
function, which is commonly found in
Other database products.
Here are a couple of solutions from June Tong which provide this
functionality via stored procedures.
===========================================================================
Here are two ways to convert character strings to all upper case.
The first way consists of a combination of 3 stored procedures. It is
the more flexible, as all three procedures are
useful utilities on their own; however, it is also slower, since two
stored procedures must be called for each
character in the string.
For sheer speed, try the second way.
=================== SPL: Upper() ===================
--
-- Procedure: GetCharAt()
-- Get a character from a string at a specified position.
--
DROP PROCEDURE GetCharAt;
CREATE PROCEDURE GetCharAt(str VARCHAR(255), pos INTEGER)
RETURNING VARCHAR(1); DEFINE i INTEGER;
IF pos > 1 THEN
FOR i = 2 TO pos
LET str = str[2,255];
END FOR;
END IF
RETURN str[1,1];
END PROCEDURE;
--
-- Procedure: Upper()
-- Convert a string to uppercase.
--
DROP PROCEDURE Upper;
CREATE PROCEDURE Upper(str VARCHAR(255))
RETURNING VARCHAR(255, 0); DEFINE i INTEGER;
DEFINE len INTEGER;
DEFINE retstr VARCHAR(255);
IF str IS NULL THEN
RETURN NULL;
ELSE
LET len = LENGTH(str);
LET retstr = '';
FOR i = 1 TO len
LET retstr = retstr||ToUpper(GetCharAt(str, i));
END FOR;
LET retstr = retstr[2,255]; -- BUG: req'd to strip off leading
blank
RETURN retstr;
END IF;
END PROCEDURE;
--
-- Procedure: ToUpper()
-- Convert a single character to upper case.
--
DROP PROCEDURE ToUpper;
CREATE PROCEDURE ToUpper(fromchar VARCHAR(1,1))
RETURNING VARCHAR(1,1); IF fromchar = 'a' THEN
RETURN 'A';
ELIF fromchar = 'b' THEN
RETURN 'B';
ELIF fromchar = 'c' THEN
RETURN 'C';
ELIF fromchar = 'd' THEN
RETURN 'D';
ELIF fromchar = 'e' THEN
RETURN 'E';
ELIF fromchar = 'f' THEN
RETURN 'F';
ELIF fromchar = 'g' THEN
RETURN 'G';
ELIF fromchar = 'h' THEN
RETURN 'H';
ELIF fromchar = 'i' THEN
RETURN 'I';
ELIF fromchar = 'j' THEN
RETURN 'J';
ELIF fromchar = 'k' THEN
RETURN 'K';
ELIF fromchar = 'l' THEN
RETURN 'L';
ELIF fromchar = 'm' THEN
RETURN 'M';
ELIF fromchar = 'n' THEN
RETURN 'N';
ELIF fromchar = 'o' THEN
RETURN 'O';
ELIF fromchar = 'p' THEN
RETURN 'P';
ELIF fromchar = 'q' THEN
RETURN 'Q';
ELIF fromchar = 'r' THEN
RETURN 'R';
ELIF fromchar = 's' THEN
RETURN 'S';
ELIF fromchar = 't' THEN
RETURN 'T';
ELIF fromchar = 'u' THEN
RETURN 'U';
ELIF fromchar = 'v' THEN
RETURN 'V';
ELIF fromchar = 'w' THEN
RETURN 'W';
ELIF fromchar = 'x' THEN
RETURN 'X';
ELIF fromchar = 'y' THEN
RETURN 'Y';
ELIF fromchar = 'z' THEN
RETURN 'Z';
END IF;
RETURN fromchar;
END PROCEDURE;
=================== SPL: Upper() ===================
--
-- Procedure: Upper()
-- Convert a string to uppercase.
--
DROP PROCEDURE upper;
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';
ELIF str[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;
June Tong:
I should mention that these both strip a leading character off the
converted value before returning it. This is