We have a lot of columns of type char and going to change them to
varchar where we expect to have a lot of trailing blanks in.

Because we do not want NULL values we cannot directly use rtrim().
So to get at least one blank the function we use is this:
CREATE FUNCTION MBI_RTRIM( source_string VARCHAR(255),
pad_string VARCHAR(255) DEFAULT ' ' ) RETURNING VARCHAR(255)
DEFINE result_string VARCHAR(255);
LET result_string = RTRIM( source_string, pad_string );
IF LENGTH( result_string ) = 0 THEN
LET result_string = ' ';
END IF;
RETURN result_string;
END FUNCTION;

For 714449 rows this took 2m 20.91s if I update two columns and 1m
31.59s for only one column.
I think these values are not to bad, but as I said there are a lot of
tables and a lot of columns and a limited time frame to to the version
upgrade of our software which includes the schema change and the data
update.
Any thought how to make this more elegant or more performant?

NVL(RTRIM(column), ' ')
?

On Sun, Mar 14, 2010 at 10:34 PM, Frank Langelage <frank (AT) lafr (DOT) de> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...