arkiboys


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.




Re: convert varchar to decimal

hunchback


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






Re: convert varchar to decimal

arkiboys

How do I fix the sql then please




Re: convert varchar to decimal

Manivannan.D.Sekaran

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






Re: convert varchar to decimal

hunchback

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





Re: convert varchar to decimal

hunchback

Did you check the link I attached to the post

AMB





Re: convert varchar to decimal

Manivannan.D.Sekaran

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






Re: convert varchar to decimal

hunchback

Hi Manivannan.D.Sekaran,

Much better. Try adding:

(

...

union all

select '$9'

) as t

AMB