Posted by Steve Kass on 02/25/07 18:17

Erland,

I've submitted a workaround to this Feedback item. If you
concatenate the empty string '' to the CHAR value before
REPLACE is applied, the hidden conversion to VARCHAR
retains the trailing blanks.

declare @t char(6)
set @t = 'A'
select
replace(@t,space(1),'*'),
replace(@t+'',space(1),'*')

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- C70DF007-1034-489C-A71E-108FBC89D553


Erland Sommarskog wrote:
> Erland Sommarskog (esquel@sommarskog.se) writes:
>
>> (paulmac106@gmail.com) writes:
>>
>>>thanks that worked great.
>>>
>>>any idea why this doesn't work:
>>>
>>>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>>
>>>it just won't seem to put the zero in....very strange
>>>
>>>i get this for 15: '15 '
>>
>>Good question. Seems like the reailing spaces are stripped when the
>>string is passed to replace(). Probably, because there is a conversion
>>to varchar, but trailing spaces should be retained, as long as the
>>setting ANSI_PADDING is in effect.
>>
>>It looks like a bug to me.
>
>
> For what it's worth, I submitted
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259840
>
> But since it works this way in SQL 2000, I would not really expect
> any fix. It could break existing code.
>
>

[Back to original message]