Converting varchar to decimal

13 May 2005 9:50 PM

Terri

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.

13 May 2005 10:06 PM

Jens Süßmeyer

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(@Number2Convert)-1)))

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Terri" <te***@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.
>
>
>

13 May 2005 11:12 PM

Terri

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 Süßmeyer" <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)))
Show quote

>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
> "Terri" <te***@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.
> >
> >
> >
>
>

13 May 2005 10:23 PM

oj

something like this should do:

select str(your_col,18,2)
from tb

--
-oj

Show quote

"Terri" <te***@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.
>
>
>

13 May 2005 11:21 PM

CBretana

Terri

Use Cast Function.. Example

Select Cast('+000000063451473.38' as Decimal(38,3))

Charly

Show quote

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