Re: SQL/INFORMIX (UPPER)
Here's another version of the upper SPL, similar to the one that
Mark Stock posted, but it doesn't require an additional call to
the trim function. I take no credit for this function - I don't
know who came up with the basic idea, but it is very clever. I
have modified it slightly and added some native character handling
It's not fast but it does acceptably well for smallish tables which
are the ones that usually contain names. Otherwise a separate column
like Nils mentioned is ugly but does the trick. Here's upper:
create procedure upper(str char(255)) returning varchar(255);define retstr varchar(255);
define x char(1);
define i,len smallint;
let retstr = '';
let len = length(str);
for i = 1 to len
if str[1,1] between "a" and "z" then
if str[1,1] = "a" then let x = "A";
elif str[1,1] = "b" then let x = "B";
elif str[1,1] = "c" then let x = "C";
elif str[1,1] = "d" then let x = "D";
elif str[1,1] = "e" then let x = "E";
elif str[1,1] = "f" then let x = "F";
elif str[1,1] = "g" then let x = "G";
elif str[1,1] = "h" then let x = "H";
elif str[1,1] = "i" then let x = "I";
elif str[1,1] = "j" then let x = "J";
elif str[1,1] = "k" then let x = "K";
elif str[1,1] = "l" then let x = "L";
elif str[1,1] = "m" then let x = "M";
elif str[1,1] = "n" then let x = "N";
elif str[1,1] = "o" then let x = "O";
elif str[1,1] = "p" then let x = "P";
elif str[1,1] = "q" then let x = "Q";
elif str[1,1] = "r" then let x = "R";
elif str[1,1] = "s" then let x = "S";
elif str[1,1] = "t" then let x = "T";
elif str[1,1] = "u" then let x = "U";
elif str[1,1] = "v" then let x = "V";
elif str[1,1] = "w" then let x = "W";
elif str[1,1] = "x" then let x = "X";
elif str[1,1] = "y" then let x = "Y";
else let x = "Z";
end if
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
elif str[1,1] = "'" then let x = "'";
else let x = str[1,1];
end if
if i = 1 then let retstr = x;
else let retstr = retstr || x;
end if
let str = str[2,255];
end for
return retstr;
end procedure;
Example of usage:
select * from sometable where upper(name) = "NAOMI"
----------------------------------------------------------------------John H. Frantz Power-4gl: Extending Informix-4gl
frantz@centrum.is http://www.rl.is/~john/pow4gl.html
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet