Convert $ to varchar decimal problem

I'm trying to convert a check amount to a fixed length string with
leading zeros and no decimal point. All is well, except for the pesky
decimal point. Here is what I have:
COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
(CONVERT(varchar(12),ckamt))
Thanks for any ideas on how to accomplish this.What exactly do you need? Could you give an example of the expected result?
E.g. I have 3.04, I want 003...
ML
http://milambda.blogspot.com/|||Multiply the number by 100 (or which ever multiple of 10 will remove the
decimal place), turn that number into an integer and then convert it into a
string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
Ta,
M. E. Houston
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Take a look at this
declare @.d decimal(12,2)
select @.d =3.04
select @.d, right('000000000000' +
convert(varchar,replace(@.d,'.','')),12)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
birdbyte@.gmail.com wrote:
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.|||Try,
declare @.m money
declare @.i int
set @.m = 12345.54
set @.i = 12
select replace(str(round(@.m, 0, 1), @.i, 0), ' ', '0')
go
AMB
"birdbyte@.gmail.com" wrote:

> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Replicate to 13, and then REPLACE({your stuff below}, '.', '')
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||I don't think this this idea is quite right. If you REPLACE() the decimal in
the decimal value, it will round.
I think you need to REPLACE() the decimal after it is converted to a
varchar().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151514826.136274.129340@.x69g2000cwx.googlegroups.com...
> Take a look at this
> declare @.d decimal(12,2)
> select @.d =3.04
> select @.d, right('000000000000' +
> convert(varchar,replace(@.d,'.','')),12)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> birdbyte@.gmail.com wrote:
>|||Or convert the decimal to an int then convert to varchar. These two
methods are assuming you want to always round down.
On Wed, 28 Jun 2006 11:52:35 -0500, "M. E. Houston"
<m.e.houston@.gmail.com> wrote:

>Multiply the number by 100 (or which ever multiple of 10 will remove the
>decimal place), turn that number into an integer and then convert it into a
>string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
>Ta,
>M. E. Houston
><birdbyte@.gmail.com> wrote in message
>news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
>|||Great suggestion. Thanks.
Arnie Rowland wrote:
> Replicate to 13, and then REPLACE({your stuff below}, '.', '')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <birdbyte@.gmail.com> wrote in message
> news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...