Searching for a trim() function
Okay, I give up! How can I return a character string without trailing
spaces in order to concat it with another character string? Mind you,
I have been able to do this using varchar columns but I've been told
that varchars cost big on performance. Our tables often exceed 5M rows.
I am on Informix 5.
Now that I asked the question bluntly, let me give a little background.
In the absence of a trim() function, I went searching for an alternate
method of concatenating 2 columns with only 1 space separating them.
I have tried...
1) varchar... worked great but folks tell me that varchar only stores a
pointer to the data instead of the actual character string. Therefore,
large queries suffer thhe overhead of the look-up.
2) stored procedure (first try)... I tried the following stored procedure
(part 1) and SQL statement (part 2). This returned something to the affect
of: 'JOHN ALEXANDER'. Not what I wanted!!!
(part 1)
create procedure trim(string char(25))
returning varchar(25); define ret_string varchar(25);
let ret_string=string;
return ret_string;
end procedure
(part 2)
select trim(c_first_name)||' '||c_last_name as full_name
from mwb_gst_data
3) stored procedure (second try)... Slightly different approach! Same
results: 'JOHN ALEXANDER'. Not what I wanted!!!
(part 1)
create procedure trim(string char(25))
returning varchar(25);
define ret_string varchar(25);
define tmp_string varchar(25);
let tmp_string=string;
let ret_string=tmp_string||"\\0";
return ret_string;
end procedure
(part 2)
select trim(c_first_name)||' '||c_last_name as full_name
from mwb_gst_data
4) stored procedure (third try)... Way different approach! Same
results: 'JOHN ALEXANDER'. Not what I wanted!!!
(part 1)
create procedure trim(str1 char(25),str2 char(25))
returning varchar(30);
define ret_string varchar(30);
define tmp_str1 varchar(30);
define tmp_str2 varchar(30);
let tmp_str1=str1;
let tmp_str2=str2;
let ret_string=tmp_str1||tmp_str2;
return ret_string;
end procedure
(part 2)
select trim(c_first_name,c_last_name) from mwb_gst_data
5) substring approach... Not even closs. It gave me a syntax error!
select c_first_name[1,length(c_first_name)]||c_last_name
from mwb_gst_data
Stored procedures seem to ignore the varchar ability to return variable
length strings and simply return the maximum length of the varchar
variable. Does any of this make sense? Are varchars stored differently
internally than chars? I thought a char(30) and a varchar(30) each reserved
30 bytes in the table but the char would pad with blanks and the varchar
would append a null character. Am I wrong (probably ;-))?
I now yeild to the collective wisdom of the net...
Chuck Ludwigsen
cludwigsen@promus.com