Re: lower function in SQL
You can write your own stored procedure to handle the lower() function.
The best solution for it is to first create a simple encoding table which
stores the conversions you need for each character. For example
create table "informix".cv_char(
code smallint not null,
iso1 char(1) not null,
lower char(1) not null,
upper char(1) not null
);
Then you can load this table using an external import file generated with
a simple C program. You do not need to index this table (no speed
improvement, this table will use one 4Kb storage page and can be scanned
faster than using an index.
Once this is done, you may write your own functions using this table
to support primitives like:
procedure "informix".ascii(iso1 varchar(255) default null) returning
integer;
procedure "informix".cv_char(code integer default null) returning
varchar(1);
procedure "informix".lower(s varchar(255) default null) returning
varchar(255);
procedure "informix".upper(s varchar(255) default null) returning
varchar(255);
Leszek Buczkowski <ls@ikp.atm.com.pl> a 'crit dans l'article
<3417CE67.3A2@ikp.atm.com.pl>...
> 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';