Subject: SQL/INFORMIX (UPPER)
Newsgroups: comp.databases.informix

From: "Mark D. Stock" <[email-masked]>
Date: Thu, 17 Apr 1997 17:06:38 +0200

> My request is :
> SELECT ROWID FROM EMP WHERE UPPER(FIRST_NAME)=UPPER('Tyler')
> UPPER is a ORACLE/SQL+ function.
> INFORMIX error is :
>  674: Procedure (upper) not found.
>  111: ISAM error:  no record found.
> Can you tell if UPPER or same function exist on INFORMIX.

You need to create the upper function like so:

--------------
	CREATE PROCEDURE upper(str CHAR(255))
                RETURNING CHAR(255);

                DEFINE  loop    INTEGER;
                DEFINE  len     INTEGER;
                DEFINE  retstr  VARCHAR(255);

                LET len = LENGTH(str);

                LET retstr = '';
                FOR loop = 1 to len
                        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';
                                ELIF str[1,1] = 'z'
                                THEN
                                        LET retstr = retstr || 'Z';
                                END IF
                        ELSE
                                LET retstr = retstr || str[1,1];
                        END IF

                        LET str = str[2,255];
                END FOR

                RETURN retstr;

        END PROCEDURE;
--------------

This was submitted by someone on this group a while ago, but I can't
remember who it was.

Then you need to say:

	SELECT	ROWID
	FROM	EMP
	WHERE	TRIM(UPPER(FIRST_NAME)) = 'TYLER'

The TRIM is required to get rid of those extra SPACES at the end.

If anyone has a better 'upper' function then please let me know.

Hope that helps,
-- 
    Mark.

    +------------------------------------------------------------------+
    |Mark D. Stock - The West Solutions Group    http://www.west.co.za |
    |                     The Informix FAQ is at http://www.iiug.org   |
    |mailto:[email-masked] +-----------------------------------------+
    |Tel:   +27 11 803 2151  |If it doesn't work, force it!            |
    |Fax:   +27 11 803 2189  |If it breaks, it needed replacing anyway!|
    |Cell:  +27 83 250 2325  |Well, that's how I code anyway!          |
    +------------------------+-----------------------------------------+

From: [email-masked]
Date: Fri, 18 Apr 1997 14:41:24 -0600

Here's another version of the upper SPL, similar to the one that
Mark Stock posted, but it doesn't require an additional call to
the trim function. I take no credit for this function - I don't
know who came up with the basic idea, but it is very clever. I
have modified it slightly and added some native character handling
It's not fast but it does acceptably well for smallish tables which
are the ones that usually contain names. Otherwise a separate column
like Nils mentioned is ugly but does the trick. Here's upper:

create procedure upper(str char(255)) returning varchar(255);
define retstr varchar(255);
define x char(1);
define i,len smallint;
let retstr = '';
let len = length(str);
for i = 1 to len
  if str[1,1] between "a" and "z" then
    if str[1,1] = "a" then let x = "A";
    elif str[1,1] = "b" then let x = "B";
    elif str[1,1] = "c" then let x = "C";
    elif str[1,1] = "d" then let x = "D";
    elif str[1,1] = "e" then let x = "E";
    elif str[1,1] = "f" then let x = "F";
    elif str[1,1] = "g" then let x = "G";
    elif str[1,1] = "h" then let x = "H";
    elif str[1,1] = "i" then let x = "I";
    elif str[1,1] = "j" then let x = "J";
    elif str[1,1] = "k" then let x = "K";
    elif str[1,1] = "l" then let x = "L";
    elif str[1,1] = "m" then let x = "M";
    elif str[1,1] = "n" then let x = "N";
    elif str[1,1] = "o" then let x = "O";
    elif str[1,1] = "p" then let x = "P";
    elif str[1,1] = "q" then let x = "Q";
    elif str[1,1] = "r" then let x = "R";
    elif str[1,1] = "s" then let x = "S";
    elif str[1,1] = "t" then let x = "T";
    elif str[1,1] = "u" then let x = "U";
    elif str[1,1] = "v" then let x = "V";
    elif str[1,1] = "w" then let x = "W";
    elif str[1,1] = "x" then let x = "X";
    elif str[1,1] = "y" then let x = "Y";
    else let x = "Z";
    end if
  elif str[1,1] = "á" then let x = "Á";
  elif str[1,1] = "é" then let x = "É";
  elif str[1,1] = "í" then let x = "Í";
  elif str[1,1] = "ó" then let x = "Ó";
  elif str[1,1] = "ú" then let x = "Ú";
  elif str[1,1] = "ý" then let x = "Ý";
  elif str[1,1] = "ö" then let x = "Ö";
  elif str[1,1] = "ð" then let x = "Ð";
  elif str[1,1] = "æ" then let x = "Æ";
  elif str[1,1] = "þ" then let x = "Þ";
  else let x = str[1,1];
  end if
  if i = 1 then let retstr = x;
  else let retstr = retstr || x;
  end if
  let str = str[2,255];
end for
return retstr;
end procedure;

Example of usage:
  select * from sometable where upper(name) = "NAOMI"
----------------------------------------------------------------------
John H. Frantz           Power-4gl: Extending Informix-4gl
[email-masked]        http://www.rl.is/~john/pow4gl.html

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet