Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1794

# Convert varchar to decimal with scale of 5 and precision of 15

I have values in a table that are varchar but I want them to be decimal(15,5):

a1:
-0.00101962
0.00287598
-0.000636328

When I use round(cast(a1 as real),5), I get:
-1.0200000000000001E-3
2.8800000000000002E-3
-6.4000000000000005E-4

When I use cast(cast(a1 as real) as decimal(15,5)), I get:
0.00
0.00
0.00

What do I do to get:
-0.00102
-0.00288
-0.00064

from the values?

Thank you,
Rebecca
0
• 5
• 4
• 4
• +2
1 Solution

Commented:
Try:

CAST(A1 AS DECIMAL(15,5))
0

Commented:

I just tried this on my MS SWQL Server:

create table test (decvalue varchar(20))
go
insert into test (decvalue) values('-0.00101962')
go
insert into test (decvalue) values('0.00287598')
go
insert into test (decvalue) values('-0.000636328')
go
select cast(cast(decvalue as real) as decimal(15,5)) from test

result...
-.00102
.00288
-.00064

so something odd is going wrong with yours!

[I am assuming the '-0.00288' is a typo, and it should be '0.00288']

Hope this helps,

Nikki
0

Commented:
rem: it's better to do formatting on client side
0

Commented:
Putting

declare @a1 varchar(20)

set @a1='-0.00101962'

select cast(cast(@a1 as real) as decimal(15,5))

into my query analyzer returns

-.00102

It may be that you have not declared the varchar to be long enough, or the query you are using to return the values from the table has the same problem. Can you post the code?

regards
0

Author Commented:
I don't know what's going on with mine..

For instance, when I do:
select cast(cast a1 as real) as decimal(15,5)) from table I still only get values that look like this:
0.00
0.00
-0.03
0.05

from values:
-0.00101962
0.00287598
-0.0289232
0.0515462

I have no idea 1) why it's not setting the precision to the 5th place after the decimal point and 2) that it changes sign.

Any ideas? Thanks.
0

Commented:
Can you do

select a1, cast(cast a1 as real) as decimal(15,5)) from table

and post the results

regards
0

Author Commented:
a1                                                 a1_casted
-------------------------------------------------- -----------------
-0.00101962                                        0.00
0.00287598                                         0.00
-0.000636328                                       0.00
0                                                  0.00
-0.0289232                                         -0.03
-0.000159124                                       0.00
0.0515462                                          0.05
0                                                  0.00
-0.0014656                                         0.00
0                                                  0.00
0                                                  0.00
-0.0044736                                         0.00
-0.0128225                                         -0.01
-0.0236664                                         -0.02
0.0135267                                          0.01
-0.00620476                                        -0.01
-0.0127401                                         -0.01
-0.0407546                                         -0.04
0                                                  0.00
0.00131889                                         0.00
1.89387                                            1.89
0                                                  0.00
-0.00398554                                        0.00
0

Commented:
What happens if you don't cast it to real but straight to decimal(15,5)

select cast(a1 as decimal(15,5))

Do you get the same result?
0

Author Commented:
"Error converting data type varchar to numeric"

.. and I think this is do to the values that have "E" in them, like this:
9.55467e-005

.. because up until this value, the values are:

-0.00101962
0.00287598
-0.000636328
0
-0.0289232
-0.000159124
0.0515462
0
-0.0014656
0
0
-0.0044736
-0.0128225
-0.0236664
0.0135267
-0.00620476
-0.0127401
-0.0407546
0
0.00131889
1.89387
0
-0.00398554
0
0.0383467
0.0030035
0
0.0386062
0.0108751
-0.346154
0
0
0
-0.00359704
-0.0247701
0
0.00510296
-0.0028075
0.0023766
-0.00792811
-0.0108852
0.0220472

.. and all seem to convert just fine.

What do you think? Thanks!

0

Commented:
Try this:

SELECT CASE WHEN CAST(A1 AS REAL) < 0.00000 THEN 0.00000 ELSE CAST(A1 AS DECIMAL(15,5)) END
0

Author Commented:
Got "Error converting data type varchar to numeric" again. It did the first 42 values just fine, until value 43 which has the "e" in it:
9.55467e-005

0

Commented:
Ok I put the first few into a table on mine and running

select a1, cast(cast(a1 as real) as decimal(15,5)) from vtabs1

I get

-0.00101962      -.00102
0.00287598      .00288
-0.000636328      -.00064
0      .00000
-0.0289232      -.02892
-0.000159124      -.00016

But I might have the answer, I presume you are getting these results in Query Analyzer

Go into Tools > Options > Connections

have you got use regional settings when displaying.... switched on ? if you have turn it of

If I turn it on I get the following results

-0.00101962      0.00
0.00287598      0.00
-0.000636328      0.00
0      0.00
-0.0289232      -0.03
-0.000159124      0.00

regards
0

Commented:
Try this:

SELECT CASE WHEN CHARINDEX('E', A1) != 0 THEN CAST(CAST(A1 AS REAL) AS DECIMAL(15,5)) ELSE CAST(A1 AS DECIMAL(15,5)) END

But I think pcsentinel found your problem.
0

Author Commented:
Yes! Thank you very much.

So when I convert these then they'll convert to what is shown when displaying regional settings is turned off?
0

Commented:
Yes

regards
0

## Featured Post

• 5
• 4
• 4
• +2
Tackle projects and never again get stuck behind a technical roadblock.