upper() - NOT the usual FAQ :-)
Hi!
I've got hold of the upper() SPL scripts from the IIUG archive, but they
aren't working for me :-(
The create procedure script I used is below the line of hash. If I run the
following script:
execute procedure upper ('asdfgh')
I get:
(expression) A
If I change the SP so that it returns 'i', I get:
(expression) A
(expression) 6
So, the for loop is being run for each letter in the input string, but only
the first letter is being put into the returned string. I'm probably
overlooking something really simple, but I just can't see the problem.
Can anyone help. My system details are in my sig.
######################################################
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;
#####################################################
Richard Thomas -------- The Music Store -------- Brentford, UK
OnLine 5.03.UC2 / SCO Openserver 5.0.2 / Twin P133 with SCO SMP
3rd party mail order application (4GL). In house reports (SQL/ACE/sh.)
Plan: learn 4GL, Java and Omnis (now what shall I do next week?)