Oninit cdi / Archive
Talk to an expert →

improve user defined function for cutting of trailing blanks

Posted in 2010

Topics: Installation, Setup & Upgrades, Stored Procedures & SPL, Data Types & Schema Design

Frank Langelage — — source: Usenet: comp.databases.informix

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?

↪ replying to Frank Langelage
Fernando Nunes — — source: Usenet: comp.databases.informix

NVL(RTRIM(column), ' ') ? On Sun, Mar 14, 2010 at 10:34 PM, Frank Langelage <frank@lafr.de> wrote: > 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? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...