Convert / Cast to number Test - SQL Server Forums

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)

Convert / Cast to number Test

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2010-04-30 : 09:54:37

My database has a character field in which the data contains some text some numbers. What I thought I could do was a test for numeric and if it passed, then cast the field to a number. So I have this:

Case When IsNumeric(UDD.UserValue) <> 1 Then UDD.UserValue Else Cast(UDD.UserValue as numeric) End

But I keep getting a 'Error converting data type nvarchar to numeric'

Can I not do this?

TIA!

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:08:41

That's 'coz you've got both types in the same column. Your CASE is returning either the original field (Varchar) or the fields CAST as numeric - you cant have both data types in the SAME column!

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:10:47

[code]
Case When IsNumeric(UDD.UserValue) = 1 Then Cast(UDD.UserValue as numeric) ELSE NULL End AS MyNumericColumn,
Case When IsNumeric(UDD.UserValue) = 1 Then NULL Else UDD.UserValue End AS MyVarcharColumn
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 10:13:40

IsNumeric(UDD.UserValue)=1 can be used effectively by

UDD.UserValue not like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:22:07

Datatype for CAST is "numeric" so I assumed the UserValue field might have any valid "Float" data ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 10:40:39

But the column UserValue is varchar datatype

select
Case When UDD.UserValue not like '%[^0-9.]%' Then Cast(UDD.UserValue as numeric) ELSE NULL End AS MyNumericColumn,
Case When UDD.UserValue not like '%[^0-9.]%' Then NULL Else UDD.UserValue End AS MyVarcharColumn
from
(
select 'iuahsd' as UserValue union all
select '12d3' union all
select '128.7'
) as UDD

select
Case When IsNumeric(UDD.UserValue) = 1 Then Cast(UDD.UserValue as numeric) ELSE NULL End AS MyNumericColumn,
Case When IsNumeric(UDD.UserValue) = 1 Then NULL Else UDD.UserValue End AS MyVarcharColumn
from
(
select 'iuahsd' as UserValue union all
select '12d3' union all
select '128.7'
) as UDD

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:59:34

[^0-9.] wont' save you from two decimal points in the field ... although I suppose you could do


    UDD.UserValue not like '%[^0-9.]%'
AND UDD.UserValue not like '%.%.%'

but might as well sue IsNumeric() at that point and live with its annoyances ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 03:34:20

quote:
Originally posted by Kristen

[^0-9.] wont' save you from two decimal points in the field ... although I suppose you could do

UDD.UserValue not like '%[^0-9.]%'
AND UDD.UserValue not like '%.%.%'

but might as well sue IsNumeric() at that point and live with its annoyances ...

Yes. You are correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources