Query Syntax - Right

I have a column that has dollar amounts with the dollar sign present (i.e. $500), due to bulk insert.
I need to convert the values in that column to Float, but I get error converting varchar to float, due to the $.
I can't do something like right(amount, 3), because the amounts aren't consistent ($500, $5000, $500000) . . .
Is there a way to select right minus one? or left 2 and on?
Help appreciated.
Thanks!
Ysandre,
You need to convert it to MONEY first, then to FLOAT:
SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
That said, why are you using FLOAT? I recommend you use DECIMAL instead;
FLOAT is an inexact type and you could end up with rounding errors. That is
not the case with DECIMAL.
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> I have a column that has dollar amounts with the dollar sign present (i.e.
$500), due to bulk insert.
> I need to convert the values in that column to Float, but I get error
converting varchar to float, due to the $.
> I can't do something like right(amount, 3), because the amounts aren't
consistent ($500, $5000, $500000) . . .
> Is there a way to select right minus one? or left 2 and on?
> Help appreciated.
> Thanks!
>
|||Thank you adam! that worked!
Our developer is the one who made everything float, I don't know why I just work with it
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
"Adam Machanic" wrote:

> Ysandre,
> You need to convert it to MONEY first, then to FLOAT:
> SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
> That said, why are you using FLOAT? I recommend you use DECIMAL instead;
> FLOAT is an inexact type and you could end up with rounding errors. That is
> not the case with DECIMAL.
>
> "Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
> news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> $500), due to bulk insert.
> converting varchar to float, due to the $.
> consistent ($500, $5000, $500000) . . .
>
>