Newsgroups: comp.databases.informix Subject: HELP upper(col) FUNCTION needed From: Christoph Schiffer Date: Fri, 22 Mar 1996 10:07:28 +0100 I need a function or any hint to do the following : select col from table where upper(col) = "UPPERLETTER" a query with a search String "ABCD" should retrieve "AbcD" independent from the spelling in the Table. I tried it with stored Procedures but here i can't use the substring with variables. Thanks in Advance -- +++++++++++++++++++++++++++++++++ ------------------------------------ greetings from Christoph Schiffer \ / Voice : +49 2104 9385-18 chris@csl-gmbh.net o FAX : +49 2104 9385-55 CSL-GmbH Erkrath / \ http://www.csl-gmbh.net --------------------------------- ++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: Mike Aubury Subject: Re: HELP upper(col) FUNCTION needed Date: Mon, 01 Apr 1996 10:14:26 GMT In article: <31526DD0.69BD@csl-gmbh.net> Christoph Schiffer > Content-Type: text/plain; charset=iso-8859-1 > Content-Transfer-Encoding: 8bit > X-Mailer: Mozilla 2.0 (Win16; I) > > I need a function or any hint > to do the following : > > select col from table > where upper(col) = "UPPERLETTER" > > a query with a search String "ABCD" I posted this some time ago - maybe an appendix in the FAQ :-) ? ------------------------------------------------------------------ There are several methods for doing an UPSHIFT function. This one seems to work OK for online because the reads from the table are cached (they are so small that all the rows should fit into a single page 26*2*1 Byte=52 Data Bytes 26*2*(4+1)=260 (approx.?) for the Index Bytes Another way is to re-write the toupper function with 26 IF THEN... END IF's If you dont have performance problems - the way used below is probably better PLEASE NOTE: I dont have any problem with anybody using these stored procedures for any purpose (other than for the purposes of vilifying informix obviously!) But I would appreciate it if you could make sure that any attributions (?) be made to: Mike Aubury of Aubit Computing Ltd (mike@aubury.demon.co.uk) {------------------------CUT HERE------------------------------------} { Because there are no CHR/ASC functions (that I have found) in SPL - the easiest way to get the upshifted letter is to look it up: } create table chrtab ( l char(1), u char(1) ); {These are optional - but I found that they DID improve performance} create unique index aubit_1 on chrtab (l); create unique index aubit_2 on chrtab (u); insert into chrtab values("a","A"); insert into chrtab values("b","B"); insert into chrtab values("c","C"); insert into chrtab values("d","D"); insert into chrtab values("e","E"); insert into chrtab values("f","F"); insert into chrtab values("g","G"); insert into chrtab values("h","H"); insert into chrtab values("i","I"); insert into chrtab values("j","J"); insert into chrtab values("k","K"); insert into chrtab values("l","L"); insert into chrtab values("m","M"); insert into chrtab values("n","N"); insert into chrtab values("o","O"); insert into chrtab values("p","P"); insert into chrtab values("q","Q"); insert into chrtab values("r","R"); insert into chrtab values("s","S"); insert into chrtab values("t","T"); insert into chrtab values("u","U"); insert into chrtab values("v","V"); insert into chrtab values("w","W"); insert into chrtab values("x","X"); insert into chrtab values("y","Y"); insert into chrtab values("z","Z"); {add more for the larger European Languages.. :-> john@rl.is } {Now for the stored procedures} {--------------------------------------------------------------------} {This one upshifts a single character} create procedure toupper(fromchr char(1)) returning char(1); define tochr char; if fromchr>="a" and fromchr<="z" then {may need changes for other alphabets} select u into tochr from chrtab where l=fromchr; else let tochr=fromchr; end if; return tochr; end procedure; {--------------------------------------------------------------------} {This one upshifts in blocks of 10 characters by calling the previous procedure } create procedure upshift_b(aa char(10)) returning char(10); define b char(10); let b=""; let b[1]=toupper(aa[1]); let b[2]=toupper(aa[2]); let b[3]=toupper(aa[3]); let b[4]=toupper(aa[4]); let b[5]=toupper(aa[5]); let b[6]=toupper(aa[6]); let b[7]=toupper(aa[7]); let b[8]=toupper(aa[8]); let b[9]=toupper(aa[9]); let b[10]=toupper(aa[10]); return b; end procedure; {----------------------------------------------------------------} {This does the actual upshifting For simplicity the whole string is broken up into groups of 10 and each set of ten is processed seperatly, you may find it more efficient to do more/less characters at one depending on the typical size of the fields in you database. } create procedure upshift(aa varchar(100)) returning varchar(100); define retstr varchar(100); let retstr=""; let retstr[1,10]=upshift_b(aa[1,10]); if length(aa)>10 then let retstr[11,20]=upshift_b(aa[11,20]); if length(aa)>20 then let retstr[21,30]=upshift_b(aa[21,30]); if length(aa)>30 then let retstr[31,40]=upshift_b(aa[31,40]); if length(aa)>40 then let retstr[41,50]=upshift_b(aa[41,50]); if length(aa)>50 then let retstr[51,60]=upshift_b(aa[51,60]); if length(aa)>60 then let retstr[61,70]=upshift_b(aa[61,70]); if length(aa)>70 then let retstr[71,80]=upshift_b(aa[71,80]); if length(aa)>80 then let retstr[81,90]=upshift_b(aa[81,90]); if length(aa)>90 then let retstr[91,100]=upshift_b(aa[91,100]); end if; end if; end if; end if; end if; end if; end if; end if; end if; return retstr; end procedure; {--------------------------------------------------------------------} { The following is a slight tweak that MAY improve performance by removing the extra procedure calls... This procedure replaces "toupper" and "upshift_b" and has been purposly commented out. } { create procedure upshift_b(fromchr char(10)) returning char(10); define tochr char(10); let tochr=null; select u into tochr from chrtab where l=fromchr[1]; if tochr[1] is null then let tochr[1]=fromchr[1]; end if; select u into tochr[2] from chrtab where l=fromchr[2]; if tochr[2] is null then let tochr[2]=fromchr[2]; end if; select u into tochr[3] from chrtab where l=fromchr[3]; if tochr[3] is null then let tochr[3]=fromchr[3]; end if; select u into tochr[4] from chrtab where l=fromchr[4]; if tochr[4] is null then let tochr[4]=fromchr[4]; end if; select u into tochr[5] from chrtab where l=fromchr[5]; if tochr[5] is null then let tochr[5]=fromchr[5]; end if; select u into tochr[6] from chrtab where l=fromchr[6]; if tochr[6] is null then let tochr[6]=fromchr[6]; end if; select u into tochr[7] from chrtab where l=fromchr[7]; if tochr[7] is null then let tochr[7]=fromchr[7]; end if; select u into tochr[8] from chrtab where l=fromchr[8]; if tochr[8] is null then let tochr[8]=fromchr[8]; end if; select u into tochr[9] from chrtab where l=fromchr[9]; if tochr[9] is null then let tochr[9]=fromchr[9]; end if; select u into tochr[10] from chrtab where l=fromchr[10]; if tochr[10] is null then let tochr[10]=fromchr[10]; end if; return tochr; end procedure; } {-----------------------END OF FILE!----------------------} -- Mike Aubury ------------------------------------------------------------------------ Shouldn't there be a shorter word for monosyllabic ? ------------------------------------------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: junet@informix.com (June Tong) Subject: Re: How Do I Convert a MiXeD-CaSeD column to UPPER CASE? Date: 26 Aug 1996 23:13:02 GMT 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 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; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: danted@nyc.pipeline.com(Ted D) Subject: Re: How Do I Convert a MiXeD-CaSeD column to UPPER CASE? Date: 27 Aug 1996 03:19:39 GMT We have had some performance problems with the solution stated by June Tong. While using the upper case conversion just for string comparison purpose, the "matches" Informix function worked a lot better. Regards --Ted D ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++