Subject Re: [firebird-support] Concatenation; unwanted spaces
Author Helen Borrie
At 11:39 PM 12/10/2005 -0400, you wrote:
>When I use the concatenation operator || in a query, the result set appears
>to add a number of spaces where the operator was used, and another single
>space at the end of the concatenated expression.

When you declare string fields as CHAR instead of VARCHAR, it is fixed
length, and the engine takes it as a requirement that the column must
contain exactly the defined number of characters. It obligingly
right-fills the "empty" members of the string array with blank characters
(e.g. ascii 32 for an ascii charset). That makes the blanks significant
characters in your data. That's one of several reasons why CHAR is not
suitable for storing variable length data. You should only use char to
store data of known fixed length, such as barcode digits and such, or if,
for example, you are storing binary sequences of a fixed number of bytes.

>I cleared the spaces in application logic. Wondered if there is a way to
>prevent this behavior.

You could employ a UDF such as RTRIM to remove the trailing blanks before
you do the concatenation.

The preventive fix for the long haul would be to redefine all your variable
length string columns as VARCHAR:

alter ATable
alter StringColumn TYPE varchar(n)
(where n is the same or larger than the defined size of the CHAR(n)

Commit that, then

update ATable
set StringColumn = RTRIM(StringColumn)
where StringColumn is not null