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

• Next in forum: Query for the first and latest wish
• Prev in forum: Re: Restore SQL DB with correct logical file names
• Thread view: Re: Format money value as padded string

[Reply to this message]