IIUG Software Repository Files

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

From: Christoph Schiffer <chris@csl-gmbh.net>
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 <mike@aubury.demon.co.uk>
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)



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.