Format money value as padded string

[paulmac106@gmail.com]

Ok my last formatting question.

How can I insert a money value as a padded string in another table?

example $1.25 gets inserted to another table as 00000125

I want 8 total characters and no decimal

another example would be 4,225.99 becomes 00422599

can this be done?

thank you!!

[Roy Harvey]

Re: Format money value as padded string

declare @m money
set @m = $1.25

select @m, RIGHT(REPLICATE ('0',8) +
convert(varchar (8),convert(int ,@m*100)),8)

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 10:15:08 -0800, paulmac106@gmai l.com wrote:

>Ok my last formatting question.
>
>How can I insert a money value as a padded string in another table?
>
>example $1.25 gets inserted to another table as 00000125
>
>I want 8 total characters and no decimal
>
>another example would be 4,225.99 becomes 00422599
>
>can this be done?
>
>thank you!!

[Utahduck@hotmail.com]

Re: Format money value as padded string

On Feb 22, 11:33 am, Roy Harvey <roy_har...@sne t.netwrote:

declare @m money
set @m = $1.25
>
select @m, RIGHT(REPLICATE ('0',8) +
convert(varchar (8),convert(int ,@m*100)),8)
>
Roy Harvey
Beacon Falls, CT
>
On 22 Feb 2007 10:15:08 -0800, paulmac...@gmai l.com wrote:
>

Ok my last formatting question.

>

How can I insert a money value as a padded string in another table?

>

example $1.25 gets inserted to another table as 00000125

>

I want 8 total characters and no decimal

>

another example would be 4,225.99 becomes 00422599

>

can this be done?

>

thank you!!

I had a similar project. I did this and it works great:

REPLACE(REPLACE (CONVERT(char(8 ), @m), '.', ''), ' ', '0')

-Utah

[paulmac106@gmail.com]

Re: Format money value as padded string

thanks that worked great.

any idea why this doesn't work:

REPLACE(CONVERT (CHAR(4),SUM(tb lLines.fldUnits )),' ','0')

it just won't seem to put the zero in....very strange

i get this for 15: '15 '

[Erland Sommarskog]

Re: Format money value as padded string

(paulmac106@gma il.com) writes:

thanks that worked great.
>
any idea why this doesn't work:
>
REPLACE(CONVERT (CHAR(4),SUM(tb lLines.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@sommarsk og.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

[paulmac106@gmail.com]

Re: Format money value as padded string

i got it to work using:

RIGHT('0000'+RE PLACE(SUM(tblLi nes.fldUnits), ' ', '0'), 4)

results in 0015 where SUM(tblLines.fl dUnits)=15

[Erland Sommarskog]

Re: Format money value as padded string

Erland Sommarskog (esquel@sommars kog.se) writes:

(paulmac106@gma il.com) writes:

>thanks that worked great.
>>
>any idea why this doesn't work:
>>
>REPLACE(CONVER T(CHAR(4),SUM(t blLines.fldUnit s)),' ','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@sommarsk og.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

[Steve Kass]

Re: Format money value as padded string

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,spac e(1),'*'),
replace(@t+'',s pace(1),'*')

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


Erland Sommarskog wrote:

Erland Sommarskog (esquel@sommars kog.se) writes:
>

>(paulmac106@gm ail.com) writes:
>>

>>>thanks that worked great.
>>>
>>>any idea why this doesn't work:
>>>
>>>REPLACE(CONV ERT(CHAR(4),SUM (tblLines.fldUn its)),' ','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.
>
>