Convert Varchar to Decimal - 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 Varchar to Decimal

Author

Topic

raguyazhin
Posting Yak Master

105 Posts

Posted - 2013-02-13 : 04:49:02

Hi,

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

This Query Run some results and with Arithmetic overflow error converting varchar to data type numeric. Error

Each time the query running with various Record counts and with the Arithmetic overflow error.

Kindly Suggest Me

-- Ragu Thangavel

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:13:28

check the values in LHP.LHP_PARAM_RESULT_VALUE and see if they can be included within precision value of 18 and scale 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raguyazhin
Posting Yak Master

105 Posts

Posted - 2013-02-13 : 06:41:36

I expect 6400 records without error. But here i ran the query 200 Records came with Arithmetic overflow error converting varchar to data type numeric Error After i ran the same query 600 records are came with error again i ran the query 0 records with the error.

so what is the problem with the query are SQL Server

Sql Server Version is SQL Server 2005 EE.

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 06:43:32

what does this return?


Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND 
LEN(LHP_PARAM_RESULT_VALUE) > 19

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raguyazhin
Posting Yak Master

105 Posts

Posted - 2013-02-13 : 06:57:12

This Query Returns 0 Records Because No records greater than 19
When we use cast that time the record counts are varying each time running the query..

quote:
Originally posted by visakh16

what does this return?

Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LEN(LHP_PARAM_RESULT_VALUE) > 19

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 07:02:37

what does this return?


Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND 
LHP_PARAM_RESULT_VALUE LIKE '%[^0-9,.]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raguyazhin
Posting Yak Master

105 Posts

Posted - 2013-02-13 : 07:09:42

Data's are not a problem because this query ran when i transferred the this single column data's to another table.
but error came on the original table only.

both column data types are same

-- This Query Runs Successfully this Table has only one column.

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS_TEST LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

-- This Query has error with various record counts at various running

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 07:12:58

I dont understand what you're talking about. Do you mean it ran successfully with the CAST? didnt understand what you mean by

This Query Runs Successfully this Table has only one column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources