convert varchar to decimal

When running this query, several records are returned but then sql server gives the follwing error. Can you see how it can be solved please? Thanks

select
case
when isnumeric([Column 9]) = 1 then convert(decimal(24, 4), [Column 9])
else
null
end
from
tblCEMTradeFeed

Error:

Error converting data type varchar to numeric.

You can not trust function ISNUMERIC a 100%. This function returns 1 also for values like '.', '2E3' (scientific notation), '+', '-', but not all of them can be conevrted to numeric data type.

select cast('2E3' as float)

go

select cast('2E3' as numeric(5, 2))

go

What is wrong with IsNumeric()?

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

AMB

|||How do I fix the sql then please?|||

The following expression might fix a issue,

Note:

Is numeric function will return true for “.”, “$” & etc.

Code Snippet

case when

isnumeric([Column 9]) = 1

and patindex('%[0-9]%',[column 9]) <> 0

then convert(decimal(24, 4), [Column 9])

else

null

end

|||

Hi Manivannan.D.Sekaran,

I guess the OP is expecting a decimal separator in the data, if not, why to convert to numeric(24, 4).

I think that the expression:

> and patindex('%[0-9]%',[column 9]) <> 0

will not yield the expected result. The following value will cause an error '2E3'.

select

case

when isnumeric([Column 9]) = 1 and patindex('%[0-9]%',[Column 9]) <> 0 then convert(decimal(24, 4), [Column 9])

else null end

from

(select '2E3' as [Column 9]) as t

go

AMB

|||

Did you check the link I attached to the post?

AMB

|||

Hi HUNCHBACK (padern me i can't able to get your orginal name),

Yes I agree with you. I hope STR should be a right choice to convert the string to decimal

Code Snippet

select

[Column 9],

Case When

patindex('%[0-9]%',[Column 9])<>0

Then

Case When

Isnumeric(str(replace([Column 9],'$',''),24,4)) =1

Then Cast(str(replace([Column 9],'$',''),24,4) as Numeric(24,4))

End

End

from

(

select '2E3' as [Column 9]

Union All

select '2E24' as [Column 9]

Union All

select '2E80' as [Column 9]

Union All

select '100' as [Column 9]

Union All

select '.' as [Column 9]

Union All

select '$' as [Column 9]

Union All

select '$9' as [Column 9]

Union All

select '878.8373738' as [Column 9]

Union All

Select 'mani'

) as t

|||

Hi Manivannan.D.Sekaran,

Much better. Try adding:

(

...

union all

select '$9'

) as t

AMB