Re: string conversion
This is a multi-part message in MIME format.
--------------5274315A7C1
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Stephan Stresing wrote:
>
> Hi,
> is there any kind of 'lowercase' function available in Informix OnLine
> 5 or 7. Even any functionality similar to the mentioned one would be
> greatly appreciated because we do need a possibility where the user
> can decide whether to search case insensitive or sensitive.
> (BTW we're developing with Uniface Six.)
> TIA and many regards,
A set of stored procedures can be used for this, per the attached.
--
//////////////// =======================================================
////////// // Dennis J. Pimple Informix Software, Inc.
////// / /// Principal Consultant 6300 S Syracuse Way Ste 205
///// // //// dennisp@informix.com Englewood CO 80111
//// // /////
/// // ////// recept: 303-850-0210
// // /////// direct: 303-740-5611 Opinions expressed are mine,
/ /////////// fax: 303-779-4025 and do not necessarily
//////////////// http://www.informix.com reflect those of my employer
--------------5274315A7C1
Content-Type: application/futuresplash; name="shift.spl"
Content-Transfer-Encoding: base64
Content-Disposition: inline; filename="shift.spl"
--
-- shift.sql; a set of stored procedures that allow for up/downshifting
-- of results in the database.
--
-- so we can write SQL statements like
-- SELECT ... FROM ... WHERE upshift(column) = "CODE"
--
-- SELECT ... FROM ... WHERE column = downshift("code")
--
-- UPDATE ... SET column = upshift( column ) WHERE 1 = 1
--
-- No guarantee for speed
--
-- Assembled by Dennis J Pimple (dennisp@informix.com),
-- from emails by people more clever than I
-- (especially on the getcharat() function)
--
--DROP PROCEDURE upshift;
CREATE PROCEDURE upshift(str CHAR(255))
RETURNING CHAR(255);
DEFINE i INTEGER;
DEFINE len INTEGER;
DEFINE retstr CHAR(255);
LET len = LENGTH(str);
-- set the return string to NULL
LET retstr = '';
-- loop though each character, shift it as necessary
FOR i = 1 TO len
-- we can't supscript a character string in SPL
-- like we can in 4GL
-- (i.e. retstr[1,1] is OK, but retstr[1,i] fails)
-- We use getcharat() function to get 1 character in position i
LET retstr = TRIM(retstr) || toupper( getcharat(str, i) );
END FOR;
RETURN TRIM(retstr);
END PROCEDURE;
UPDATE STATISTICS FOR PROCEDURE upshift;
--DROP PROCEDURE downshift;
CREATE PROCEDURE downshift(str CHAR(255))
RETURNING CHAR(255);
DEFINE i INTEGER;
DEFINE len INTEGER;
DEFINE retstr CHAR(255);
LET len = LENGTH(str);
LET retstr = '';
FOR i = 1 TO len
-- same song 2nd verse for lowercase letters
LET retstr = TRIM(retstr) || tolower( getcharat(str, i) );
END FOR;
RETURN TRIM(retstr);
END PROCEDURE;
UPDATE STATISTICS FOR PROCEDURE downshift;
-- getcharat
-- Take the string and return the character at position pos
-- It exists because variable subscripting (e.g. variable[x,y])
-- doesn't work in SPL
--DROP PROCEDURE getcharat;
CREATE PROCEDURE getcharat(str CHAR(255), pos INTEGER)
RETURNING CHAR(1);
DEFINE i INTEGER;
IF pos > 1 THEN
-- loop through and chop off the first letter of the string
-- until we get to the one we want.
FOR i = 2 TO pos
LET str = str[2,255];
END FOR;
END IF
RETURN str[1,1];
END PROCEDURE;
UPDATE STATISTICS FOR PROCEDURE getcharat;
-- take a lowercase letter and return its uppercase equivalent,
-- otherwise return what was sent
-- there might be some performance enhancement by ordering the letters
-- in order of common usage, but I doubt if it's much
--DROP PROCEDURE toupper;
CREATE PROCEDURE toupper(fromchar CHAR(1))
RETURNING CHAR(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;
UPDATE STATISTICS FOR PROCEDURE toupper;
-- take a uppercase letter and return its lowercase equivalent,
-- otherwise return what was sent
-- there might be some performance enhancement by ordering the letters
-- in order of common usage, but I doubt if it's much
--DROP PROCEDURE tolower;
CREATE PROCEDURE tolower(fromchar CHAR(1))
RETURNING CHAR(1);