converting varchar field to float

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 2005 Forums

Transact-SQL (2005)

converting varchar field to float

Author

Topic

noms
Starting Member

22 Posts

Posted - 2009-02-19 : 04:13:33

hi experts
i have a field called AttributeValue in my database and it's a datatype of varchar, it's stores string values as well as money and number values. i want to limit the selection to only those values which are numbers and money and convert them to float
here's my select statement
"SELECT @liImpairment = CAST(AV.[AttributeValue] AS float)*(1-(@DeprPds/@liRUL)),
@liFairValue = CAST(AV.[AttributeValue] AS float(1))-@liImpairment
FROM [stdAttributeValues] AV"

but i'm getting an error
"Error converting data type varchar to float."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-19 : 04:22:00

Add this

WHERE AV.[AttributeValue] not like '%[^0-9.]%'

Madhivanan

Failing to plan is Planning to fail

noms
Starting Member

22 Posts

Posted - 2009-02-19 : 04:33:21

you are such a star i'll give you 10 out of 10

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-19 : 04:53:43

You should use a real datatype instead like DECIMAL. Float is a floating point number and has accuracy implications: http://en.wikipedia.org/wiki/Floating_point

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-20 : 02:46:00

quote:
Originally posted by noms

you are such a star i'll give you 10 out of 10

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources