convert varchar to decimal

Question

Post reply

convert varchar to decimal

Sridhar-137443

Hall of Fame

Points: 3762
More actions
February 1, 2005 at 10:17 pm

#60394

Hi,
I am transferring oracle database to sql server. In oracle I have a column which is declared as numeric. I am transferring that table into sql server. The column so many digits after the decimal point. I transfered the data into the temp table using varchar as the data type for the column. Now I want to convert this into either decimal or money data so that I can retain exactly the same value. How can i do this?
The sample value in the column is
.0366666666666666666666666666666666666667
It contains 40 digits after decimal point. The decimal datatype cannot support this number of precision. I tried using money but it is rounding the value. Please let me know the solution for this?
Thanks,
Sridhar!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 1

you might try this :

set nocount on

create table #t1 (col1 varchar(100) not null, col2 decimal(8,2) null)

insert into #t1 (col1) values ('.0366666666666666666666666666666666666667')

select * from #t1

update #t1

set col2 = convert(decimal(8,2),substring(col1,1,CHARINDEX ( '.' , col1 , 1) + 2))

where col2 is null

select * from #t1

drop table #t1

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 2

Hi Alzdba,

I know that I can do like the one you suggested. But what I want is the same value with out rounding. that is i need the decimal value to be .0366666666666666666666666666666666666667. I looked at the maximum precision provided by sql server. it is 38. but oracle provides more precision than this. so I guess the only way would be to round to the nearest value.

Thanks for your time.

Sridhar!!

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 3

actualy I guess it is a rounded value (...6667)

You might also consider float-datatype

set nocount on

create table #t1 (col1 varchar(100) not null, col2 float null)

insert into #t1 (col1) values ('.0366666666666666666666666666666666666667')

select * from #t1

update #t1

set col2 = col1

where col2 is null

select * from #t1

drop table #t1

col1 col2

.0366666666666666666666666666666666666667 NULL