Sybase SQL - null condition, removing whitespace

Question

I've got this query below that drove me mad, would you be able to fix the code below so that the result will be like this : "123 Bridge St 2500" instead of " 123 Bridge St 2500" (extra whitespace at the beginning and in between St and 2500)?

declare @street varchar(20), @street2 varchar(20), @suburb varchar(20), @postcode varchar(4)
set @street = null
set @street2 = '123 Bridge St'
set @suburb = null
set @postcode = '2500'

select address = case @street when null then '' else @street + ' ' end
                        +case @street2 when null then '' else @street2 + ' ' end
                        +case @suburb when null then '' else @suburb + ' ' end
                        +case @postcode when null then '' else @postcode + ' ' end

-- Expected Result: '123 Bridge St 2500'
-- Actual Result: ' 123 Bridge St  2500'

Thanks!

Answer 1

Thanks for all your comments,

here's the solution that worked

select address = ltrim(rtrim(@street) + ' ') + ltrim(rtrim(@street2) + ' ') + ltrim(rtrim(@suburb) + ' ') + @postcode

Answer 2

Try

select case when @street is null then '' else @street + ' ' end
                        +case when @street2 is null then '' else @street2 + ' ' end
                        +case when @suburb is null then '' else @suburb + ' ' end
                        +case when @postcode is null then '' else @postcode + ' ' end

When you write case @street when null.. that's the same as when @street = null which doesn't work, so use is null instead.