Posted by Erland Sommarskog on 02/22/07 21:59

(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.

--
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]