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

conversion sql from oracle to SQL Server

Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..

Oracle
----
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;

Here is its SQL
------
UPDATE pricepoint
SET pp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
-- What's with this?
-- , pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null|||Try converting your pp_pricepoint variable to varchar before using the substring function

Change :

CASE SUBSTRING(pp_pricepoint_id, 1, 1)

For :

CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)

Pls Note i didnt check ur substring use for the sintax.

Hope it can help|||Thanks Brett, this worked..
UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
, pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null

I am updating 2 values here..sqlsql