Subject: HELP upper(col) FUNCTION needed
Newsgroups: comp.databases.informix

From: Christoph Schiffer <[email-masked]>
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
        [email-masked]            o       FAX   : +49 2104 9385-55
        CSL-GmbH  Erkrath           /   \     http://www.csl-gmbh.net
 ---------------------------------
++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

From: Mike Aubury <[email-masked]>
Subject: Re: HELP upper(col) FUNCTION  needed
Date: Mon, 01 Apr 1996 10:14:26 GMT

In article: <[email-masked]>  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 ([email-masked])

{------------------------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.. :-> [email-masked]   }

{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: [email-masked] (June Tong)
Subject: Re: How Do I Convert a MiXeD-CaSeD column to UPPER CASE?
Date: 26 Aug 1996 23:13:02 GMT

[email-masked] 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                  [email-masked]   ----
----       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: [email-masked](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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++