Re: How to convert a field to UPPERCASE
This isn't pretty but, here's a proc that works in 7.22.
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;
RETURN retstr;
END IF;
END PROCEDURE;
Streitberger Andreas wrote:
> Can anyone tell me how to update a field to uppercase.
> I know ANSI-SQL supports UPPER, LOWER, TRIM and SUBSTRING. But beside
> SUBSTRING the others seem to be unknown to Informix (Version 5.2....)
> Is there a way to reach this goal with a SQL-Statement??
> Any help gladly accepted
>
> Andreas STREITBERGER
> E-Mail: mailto:andreas.streitberger@systema.co.at