Re: HELP upper(col) FUNCTION needed
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 l