Solved

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

Posted on 2005-04-26
1,792 Views
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

LVL 28

Expert Comment

Try:

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

LVL 2

Expert Comment

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

LVL 12

Expert Comment

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

LVL 11

Expert Comment

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 Comment

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

LVL 11

Expert Comment

Can you do

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

and post the results

regards
0

Author Comment

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

LVL 28

Expert Comment

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 Comment

"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

LVL 28

Expert Comment

Try this:

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

Author Comment

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

LVL 11

Accepted Solution

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

LVL 28

Expert Comment

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 Comment

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

LVL 11

Expert Comment

Yes

regards
0

## Featured Post

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in beâ€¦
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.