Posted by Erland Sommarskog on 02/23/07 22:51

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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]