Format money value as padded string

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!!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@.gmail.com wrote:

Quote:

Originally Posted by

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

|||On Feb 22, 11:33 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Ok my last formatting question.

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

example $1.25 gets inserted to another table as 00000125

>

Quote:

Originally Posted by

I want 8 total characters and no decimal

>

Quote:

Originally Posted by

another example would be 4,225.99 becomes 00422599

>

Quote:

Originally Posted by

can this be done?

>

Quote:

Originally Posted by

thank you!!

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

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

-Utah|||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 '|||(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i got it to work using:

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

results in 0015 where SUM(tblLines.fldUnits)=15|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

>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/SQLSe...edbackID=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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:
>

Quote:

Originally Posted by

>(paulmac106@.gmail.com) writes:
>>

Quote:

Originally Posted by

>>>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/SQLSe...edbackID=259840
>
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>
>

Format data in a column

I need to format the data in 2 columns in a SQL view. Each column contains
numeric values. They both need to be formatted so that they are padded with
leading zeros if needed. Example: Column 1 is set to a length of 9, so if
the value of 999999 it needs to be formatted as 000999999. Column 2 is the
same way but has a lenght of 15. Can someone help me out with this? ThanksForgot to mention I am using a MS Access 2003 front end and a SQL DB.
"CD" wrote:
> I need to format the data in 2 columns in a SQL view. Each column contain
s
> numeric values. They both need to be formatted so that they are padded wi
th
> leading zeros if needed. Example: Column 1 is set to a length of 9, so i
f
> the value of 999999 it needs to be formatted as 000999999. Column 2 is th
e
> same way but has a lenght of 15. Can someone help me out with this? Thanks[/colo
r]|||Try,
update t1
set
c1 = right(replicate('0', 9) + c1, 9),
c2 = right(replicate('0', 15) + c2, 15)
AMB
"CD" wrote:
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||CD,
While we can write the T-SQL to do this, I'd recommend using Access 2003 to
handle the formatting of the data - more versatile.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||You can create a user degined function for this:
CREATE FUNCTION fn_0Int
(
@.input as int,
@.CharLength as int
)
returns varchar(255)
AS
begin
return
right(replicate('0',@.CharLength)+convert
(varchar(255),@.input),@.CharLength)
end
GO
print dbo.fn_0Int(999999,9)
000999999
print dbo.fn_0Int(999999,15)
000000000999999
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||Jerry,
I had the same thoughts, but I couldn't find where I could do this.
"Jerry Spivey" wrote:

> CD,
> While we can write the T-SQL to do this, I'd recommend using Access 2003 t
o
> handle the formatting of the data - more versatile.
> HTH
> Jerry
> "CD" <CD@.discussions.microsoft.com> wrote in message
> news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
>
>|||On Wed, 28 Sep 2005 11:55:03 -0700, CD wrote:
>I need to format the data in 2 columns in a SQL view. Each column contains
>numeric values. They both need to be formatted so that they are padded wit
h
>leading zeros if needed. Example: Column 1 is set to a length of 9, so if
>the value of 999999 it needs to be formatted as 000999999. Column 2 is the
>same way but has a lenght of 15. Can someone help me out with this? Thanks[/color
]
CREATE VIEW FormattedData
AS
SELECT RIGHT(REPLICATE('0', 9) + CAST(Column1 AS varchar), 9) AS Column1
, RIGHT(REPLICATE('0',15) + CAST(Column2 AS varchar),15) AS Column2
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)