Re: Upper and Lower Case with Informix SE 5
Many thanks for your considered responses. Unfortunately, upgrading isn't an option for a number of reasons. a) We're still using the database up until march/april next year when its being migrated over to (dare I say it) Sybase 9 (I know, don't blame me, its the software this company uses...) b) I can't add a column to the table without violating our support with the vendor c) No budget As for the IIUG scripts, I've already downloaded them and the SPL doesn't compile with VARCHARS in it. Convert these to CHARs and it compiles and runs okay, but the concatenation within the script doesn't work correctly (which led me to post the disgruntled e-mail)! e.g. DEFINE StringToConvert CHAR(64); DEFINE ConvertedString CHAR(64); LET ConvertedString = ''; LET ConvertedString = ConvertedString || (pipe pipe) StringToConvert[1,64]; This assigns '64 spaces' THEN the value of StringToConvert so, in essence, ConvertedString (CHAR64) is empty (since the StringToConvert appears to have been added at the 65th position. (and yes, it does give me a subscript out of range error when I run it). If you have a different script to the one that June Tong wrote, I'd be nore than happy (and thankful) to try it out. Much appreciated. The only other idea I had was to migrate the data to IDS 10, perform the searches then perform the updates on the SE server. But this sounds like an awful lot of work just to do case in-sensitive searches on the data!! Thanks again for your responses...
I did this on a version 10 database. You may have to replace the case
function with a string of ifs. I put a sample in the comments if you
need to change. This only uses substring so it should work. I have
never worked on version 5 so I can't vouch for everything. I also have
only tested this a little. I am doing the tests in letter frequency
order to reduce the number of tests that I do on average. But I may
have left out a letter or crossed up in my typing so you will want to
check that also.
CREATE PROCEDURE upper5(str CHAR(64))
RETURNING CHAR(64) ;
DEFINE i,j INTEGER;
DEFINE length_str INTEGER;
DEFINE retstr char(64);
define testChar, replaceChar char(1);
-- define fromLetters, toLetters char(64);
-- let fromLetters = "etaoinshrdlcumwfgypbvkjxqz" ;
-- let toLetters = "ETAOINSHRDLCUMWFGYPBVKJXQZ" ;
IF str IS NULL THEN
RETURN NULL;
ELSE
let retstr = "" ;
let length_str = length(str);
-- let length_str = 64;
FOR i = 1 TO length_str
let testChar = substring(str from i for 1);
let replaceChar = testChar;
if testChar >= 'a' and testChar <= 'z' then
-- Replace with ifs if version 5 doesn't have case statement:
-- if testChar = 'e' then
-- replaceChar = 'E'
-- else if testChar = 't' then
-- replaceChar = 'T'
-- ETC.
let replaceChar =
case testChar
when 'e' then 'E'
when 't' then 'T'
when 'a' then 'A'
when 'o' then 'O'
when 'i' then 'I'
when 'n' then 'N'
when 's' then 'S'
when 'h' then 'H'
when 'r' then 'R'
when 'd' then 'D'
when 'l' then 'L'
when 'c' then 'C'
when 'u' then 'U'
when 'm' then 'M'
when 'w' then 'W'
when 'f' then 'F'
when 'g' then 'G'
when 'y' then 'Y'
when 'p' then 'P'
when 'b' then 'B'
when 'v' then 'V'
when 'k' then 'K'
when 'j' then 'J'
when 'x' then 'X'
when 'q' then 'Q'
--when 'z' then 'z' Safer
else 'Z'
end
;
end if
let retstr = substring(retstr from 1 for i - 1) || replaceChar ;
END FOR;
RETURN retstr;
END IF;
END PROCEDURE;
select upper5("hello") from systables where tabid = 1;