Convert varchar to decimal

Hello, I am running a SELECT statement on a varchar 50 field. I would like
to convert the output to a numeric field with 2 decimal places. I tried a
CAST but that didn't work. Any suggestions would be appreciated. Thanks,
Pancho."Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.
Not knowing the largest decimal value you might wish to display, you could
do something like this:
SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
FROM TableName
This will allow a total of 10 digits with 2 on the right of the decimal
point.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Pancho,
What didn't work?
Did you receive an error message?
If so, what?
If not, show us your code.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.|||Rick,
I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
and got:
'dec' is not a recognized function name
Got the same error using 'decimal'. I am running a successful
SELECT CONVERT (CHAR (8), Field8, 112) AS Field8Text but the dec didn't work
.
"Rick Sawtell" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
> Not knowing the largest decimal value you might wish to display, you could
> do something like this:
> SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
> FROM TableName
>
> This will allow a total of 10 digits with 2 on the right of the decimal
> point.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi Raymond,
I got the msg "Unable to convert varchar field" when using CAST. I am
taking data from a varchar and trying to format it to a numeric field, 10
wide with 2 decimal spaces. Pls see code above in my reply to Rick. Thanks
,
Pancho.
"Raymond D'Anjou" wrote:

> Hi Pancho,
> What didn't work?
> Did you receive an error message?
> If so, what?
> If not, show us your code.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
>|||> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work.
What does "didn't work" mean? What did you try? Are you sure all of the
values are really numeric and can be converted? If so, why are you storing
them in a VARCHAR(50) column?|||> I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
> and got:
> 'dec' is not a recognized function name
Did you try using the right syntax?
NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)|||> I got the msg "Unable to convert varchar field" when using CAST.
Well, because you decided to use a VARCHAR(50) to store decimals, invariably
you will get varchar data that is not a decimal.
You shouldn't be really surprised by this.
A start would be to filter out the rows where ISNUMERIC(column_name) = 0.
However, ISNUMERIC() is not perfect either, see http://www.aspfaq.com/2390
Then, try cleaning up your data and fixing the data type. When you do that,
you won't need to do a convert at all.|||You got me. I didn't design this DB; a vendor did. I would not have stored
a field which has cash values as a varchar either. I'm trying to convert it
from one vendor to another.
Thanks everyone for your comments. I'm closing this one now.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "didn't work" mean? What did you try? Are you sure all of the
> values are really numeric and can be converted? If so, why are you storin
g
> them in a VARCHAR(50) column?
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OfuNFSyXGHA.4388@.TK2MSFTNGP03.phx.gbl...
> Did you try using the right syntax?
> NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)
>
Ooops.. Got it backwards. Thanks Aaron.
Rick