Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>