Re: Space-padded Strings and Stored Procedures

I've tried addressing this with the LENGTH() and SUBSTR() functions with some success as below (not exactly what I tried, but the same idea) HTH jw Paul Harman wrote: > I'm running Informix Dynamic Server Version 7.30.UC7 (straight from > onstat!). > > I have a stored procedure to perform an audit trailing function. It takes in > an ID, performs a lookup on the appropriate table using this ID to get a > "name", and then enters this name into the audit table, thusly: > > CREATE PROCEDURE valueadded(vid INT) > DEFINE valuename VARCHAR(255);> DEFINE details VARCHAR(255); DEFINE len INT; > > > SELECT LENGTH(fullname), fullname INTO len, valuename FROM value WHERE > valueid=vid; > LET details="Added " || valuename || " value"; LET details="Added " || SUBSTR(valuename, 1, len) || " value"; > > INSERT INTO audit (occurred, comment) VALUES (CURRENT, details);> END PROCEDURE; > > My problem is that the "fullname" field in the "value" table is a char 128 - > and thus gets padded out with spaces. This means that there is a tremendous > amount of surplus white space before the word 'value' in the log. > > [Obviously this example is rather artificial, but I can't give exact code > snippets] > > Is there any way of "trimming" this space? It's no problem to me when I use > Java or Perl (the main apps running on the DB) because I can .trim() or > s/\\s+$// respectively, but I can't find a similar mechanism to do this in > Stored Procedures - and for performance reasons I don't want to change the > value table to use a varchar for the fullname attribute. > > Anyone have any good ideas? Many thanks in advance, > > Paul