Subject: SQL/INFORMIX (UPPER) Newsgroups: comp.databases.informix From: "Mark D. Stock" 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:marks@west.co.za +-----------------------------------------+ |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: frantz@centrum.is 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 frantz@centrum.is http://www.rl.is/~john/pow4gl.html -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet