Re: need UCASE and TRIM functions

Item Subject: need UCASE and TRIM functions Informix does have a built-in function for trimming trailing spaces and its called trim() and this can be used in stored procedures. Below is an upper function courtesy of John Frantz -- I haven't had a chance to test it yet but maybe you can... ********************************************************************* Well I think we've finally got an upper function that works, although the performance is questionable, especially if you want to search with it. I added a couple of changes that I thought would improve it, at least for my installations. The first change is the "returning" clause where I used a varchar. I thought this might be more efficient by returning only the "meat" of the string instead of a full 255 bytes every time. The second change concerns "foreign" languages where other letters than "a" through "z" have to be upshifted. These letters are scattered about the 8-bit ascii table. Here's my iteration, compacted: drop procedure upper; create procedure upper(str char(255)) returning varchar(255); define retstr varchar(255); define x char(1); -- sorry, I didn't like "char" define i,len smallint; -- "l" is too much like i and nr. "1" 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"; ... The english alphabet ... elif str[1,1] = "y" then let x = "Y"; else let x = "Z"; -- no need to test for "z" end if elif str[1,1] = "'" then let x = "+"; ... The rest of the alphabet ... 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; --- Provided by JOHN FRANTZ ********************************************************************** Hope that helps. Regards, Anisha anisha_vaswani@hp.com Standard disclaimers apply. ______________________________ Reply Separator _________________________________ Subject: need UCASE and TRIM functions Author: Non-HP-informix-list-owner (informix-list-owner@rmy.emory.edu) at HP-Boise,shargw4 Date: 12/11/96 20:20 multi-platform software developer client/server application needs functions or stored procedures to convert char and varchar to upper case. also, to trim rightmost spaces ... i'm sure many others have solved this problem ... sybase, sql server, oracle and others have built in functions ... informix does not. help ... e-mail bbertovich@aol.com (yes, 2 b's) ... thx.