SPL Upshift Function -Reply
Michael,
Here is an alternative for your upshift and upshift_b functions. I
had the same problem as you with the lack of an ascii/ chr functions so I
went for the 26 ifs approach over the select, but they seem much the
same. To save space I have just referenced your toupper procedure.
create procedure upshift( pv_string varchar(255) ) returningvarchar(255);
define pv_char char(1);
define pv_return varchar(255);
let pv_return = "";
while length(pv_string) > 0
let pv_char = pv_string[1];
if pv_char matches "[a-z]" then
let pv_char = toupper(pv_char);
end if;
let pv_string[1] = " ";
let pv_return = trim(pv_return)||pv_char;
let pv_string = trim(pv_string);
end while;
return pv_return;
end procedure;
>>> Michael Aubury <maubury@csl.gov.uk> 7/February/1996 09:14pm
Hi all -
..snip...
There are several methods for doing an UPSHIFT function.
..snip..
{------------------------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 onchrtab (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 intochrtab 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");
{Now for the stored procedures}
{--------------------------------------------------------------------}
{This one upshifts a single character}
create procedure toupper(fromchr char(1)) returning char(1); definetochr char; if fromchr>="a" and fromchr<="z" then
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 bchar(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;
..snip..
{-----------------------END OF FILE!----------------------}