Subject Re: padding results from case expression
Author Adam
--- In, "knisco99" <knisco99@...> wrote:
> Recently while working with a new stored procedure in Firebird 1.5.3
> on linux a developer tried out using a case expression. Our java
> developer then noticed that the result was being padded with spaces
> according to the largest string in the case statement. You can see
> this same behaviour in a select query
> select strlen( case when 1=1 then 'test' when 2=0 then 'biggertest'
> else 'control' end )
> from rdb$database

Hi Scott,

The problem is that Firebird is presuming the quoted string to be of
type char, and one that is long enough to fit the longest possible
returned value (although it doesn't recognise that 2=0 can never
evaluate to true, it presumes and of the when and else is possible).

The SQL standard states that a char field must be right padded with
spaces to occupy the entire size of the field.

The easy way around this is to let Firebird off the hook and
explicitly tell it you want a varchar. You only need to do the first

This should do the trick:

when 1=1 then cast('test' as varchar(10))
when 2=0 then 'biggertest'
else 'control'
from rdb$database