Re: How Do I Convert a MiXeD-CaSeD column to UPPER CASE?
STEVE_LOYD@HP-SanJose-om1.om.hp.com wrote:
: Can anyone recommend a technique for converting mixed-cased character
: datatype fields to UPPER in an existing table without writing a
: program?
Does a stored procedure count as "writing a program"? Here are two methods
for converting a string to upper-case, using stored procedures. Are these
in the FAQ or IIUG archives or whatever? If not, I think they should be.
I should mention that these both strip a leading character off the converted
value before returning it. This is because of a bug in most currently-
available versions where concatenating anything to an empty varchar variable
caused the empty varchar to be expanded to a space, thus
DEFINE retstr VARCHAR(255);
LET retstr = '';
LET retstr = retstr || 'A';
would result in ' A'. As a result, before returning, the converted value is
first stripped of its first character, as indicated by the comment:
LET retstr = retstr[2,255]; -- BUG: req'd to strip off leading blank
If you are using a version where this bug has been fixed, you should remove
this line from the stored procedure. Be careful only to remove the line
with the comment BUG -- other incidents of this statement are required for
proper functioning of the stored procedures.
June
---- June Tong Informix Software ----
---- Senior Consultant (415) 926-6140 ----
---- International Support junet@informix.com ----
---- Location-du-jour: Oakland ----
*
* Standard disclaimers apply
*
- Please do not send me requests/questions by mail. When I have the knowledge
- and time permits, I try to answer questions on comp.databases.informix, but
- travel schedule, time, and volume make responding to personal requests
- difficult and often slow. Please call your local Informix Technical Support
- organization for assistance with technical issues.
===========================================================================
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@@NL@