By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,312 Members | 1,254 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,312 IT Pros & Developers. It's quick & easy.

Format money value as padded string

P: n/a
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!!

Feb 22 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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, pa********@gmail.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!!
Feb 22 '07 #2

P: n/a
On Feb 22, 11:33 am, Roy Harvey <roy_har...@snet.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...@gmail.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

Feb 22 '07 #3

P: n/a
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 '

Feb 22 '07 #4

P: n/a
(pa********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 22 '07 #5

P: n/a
i got it to work using:

RIGHT('0000'+REPLACE(SUM(tblLines.fldUnits), ' ', '0'), 4)

results in 0015 where SUM(tblLines.fldUnits)=15

Feb 23 '07 #6

P: n/a
Erland Sommarskog (es****@sommarskog.se) writes:
(pa********@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/SQLSer...dbackID=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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 23 '07 #7

P: n/a
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 (es****@sommarskog.se) writes:
>(pa********@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/SQLSer...dbackID=259840

But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.

Feb 25 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.