numeric data in varchar datatype

Question

Post reply

numeric data in varchar datatype

SQLAddict01

SSC Eights!

Points: 885
More actions
February 11, 2016 at 1:36 pm

#324743

I have a column with varchar data type but has numeric data in it
I know I can't use arithmetic operation for varchar
This is my query
select * from table1
where col1> 500.00
Msg 8115, Level 16, State 8, Line 38
Arithmetic overflow error converting varchar to data type numeric.
obviously it gives me this error.
what functions should I use to convert the data type?
I appreciate your help
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 1

Here you go:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

If you are on SQL Server 2012 or better, look here as well: https://msdn.microsoft.com/en-us/library/hh974669.aspx

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Y.B. SSChampion Points: 11572 More actions · Answer 2

Just a couple things to consider. Make sure you validate your data to make sure it's actually a number. i.e. (ISNUMERIC). Secondly, try to avoid using CONVERT in your WHERE clause as this can cause performance issues and prevent the use of indexes.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

yb751 (2/11/2016)
Just a couple things to consider. Make sure you validate your data to make sure it's actually a number. i.e. (ISNUMERIC). Secondly, try to avoid using CONVERT in your WHERE clause as this can cause performance issues and prevent the use of indexes.

Be careful with IsNumeric(). It can produce unexpected results (see here[/url] for why).

Possibly a better approach is to use TRY_CAST().

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

SQLAddict01 (2/11/2016)
I have a column with varchar data type but has numeric data in it
I know I can't use arithmetic operation for varchar
This is my query
select * from table1
where col1> 500.00
Msg 8115, Level 16, State 8, Line 38
Arithmetic overflow error converting varchar to data type numeric.
obviously it gives me this error.
what functions should I use to convert the data type?
I appreciate your help
Thanks

May I ask what was wrong with the solution provided yesterday?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.