Posted by meendar on 05/04/07 05:26

On May 2, 12:10 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> meendar (askjavaprogramm...@gmail.com) writes:
> > I have selected a field name and declared it as varchar, since it is
> > varchar in table and performed some numeric operation with numbers,
>
> > even after i cast the sql in below code, it throws an exception as
> > "Error converting data type varchar to numeric."
>
> >...
> > Declare r1 cursor
> > local Scroll Keyset Optimistic
> > For
> > select z1 from employee a1 where z2= @z1 + 45 .....
>
> > I want to clear that how can we cast the field with varchar for
> > numeric operations, i have also tried cast and convert to change it
> > but all in vain.
>
> SQL Server tries to convert all values in employee.z2 to numeric, and
> when this fails for some value, the query fails.
>
> You need to use the CASE expression:
>
> WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
> THEN convert(numeric, z2)
> END = @z1 + 45
>
> Now it will only attempt to convert z2 which it consists of digits only.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks to All

[Back to original message]