Convert varchar to decimal type !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert varchar to decimal type !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-01-04 : 11:42:08

Hi All,

In a Table called TABLE1 I have a column Column1 of varchar type with such kind of values
+0000011.01
How I convert column1 to decimal type(with precision 2, i.e 2 decimal places.
In above example the result should be 11.01

Any idea?

pootle_flump

1064 Posts

Posted - 2008-01-04 : 11:45:22

[code]PRINT CAST('+0000011.01' AS DECIMAL(4, 2))[/code]

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-01-04 : 12:24:10

Thanks pootle,

I am running the following query which works just fine.
select top 100 cast(column1AS DECIMAL(4, 2))
from [TABLE1]

But I change 100 to like 100 or more it gives erorr.

So the following query:

select top 1000 cast(column1AS DECIMAL(4, 2))
from [TABLE1]

Give the erorr:
Arithmetic overflow error converting varchar to data type numeric.

Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 12:32:38

Seems like there are some values existing within column that dont fit into decimal type.Use this query to check for invalid values:-
select column1
from [TABLE1]
WHERE ISNUMERIC(column1)=0

pootle_flump

1064 Posts

Posted - 2008-01-04 : 12:34:22

Sorry - misread your question:

PRINT	CAST('+0000011.01' AS DECIMAL(9, 2))

I hope you are going to change the data type for this column yah?

pootle_flump

1064 Posts

Posted - 2008-01-04 : 12:35:25

quote:
Originally posted by visakh16

Seems like there are some values existing within column that dont fit into decimal type.

They might do if I defined it correctly in the first place

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources