Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert Varchar to Decimal Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 2:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 19, 2014 9:02 PM
Points: 82, Visits: 328
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
Post #1419359
Posted Wednesday, February 13, 2013 2:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:37 AM
Points: 102, Visits: 197
You are replacing the comma with an empty string, that will result in incorrect results even without running into the overflow.

have a look at the 2 examples below, that should illustrate where your problem lies

SELECT CONVERT(DECIMAL(15,1), REPLACE('1,0000000000000001', ',',''))
SELECT CONVERT(DECIMAL(15,1), REPLACE('1,0000000000000001', ',','.'))

BR
Post #1419362
Posted Wednesday, February 13, 2013 4:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 19, 2014 9:02 PM
Points: 82, Visits: 328

The Query is running but 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
Post #1419391
Posted Wednesday, February 13, 2013 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
Please provide examples of values which will and will not convert from your table



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1419395
Posted Wednesday, February 13, 2013 5:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 19, 2014 9:02 PM
Points: 82, Visits: 328
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 <> ''
Post #1419416
Posted Wednesday, February 13, 2013 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
raguyazhin (2/13/2013)
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 <> ''


Actually the problem absolutely is the data. Don't confuse that with datatype of the structure of the table. What that means is that in one environment your query works because the VALUES are able to be converted. In the other environment there is at least one value that can't be converted. This is why it is so important to use the proper datatypes.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419563
Posted Wednesday, February 13, 2013 11:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
The problem may be as simple as redefining your DECIMAL datatype conversion. Below I do the replace and ISNUMERIC check in one pass then only CAST values that meet that criteria.


--sample data

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Col1] VARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT '123456789'
UNION
SELECT '123456789123456789'
UNION
SELECT '123,456,789,123,456,789'
UNION
SELECT NULL
UNION
SELECT ''
UNION
SELECT 'XYZ'

SELECT * FROM #TempTable


If I use DECIMAL(18,2)--the default and what you use in your question--I get the arithmetic overflow error using my sample data above. Change the size of the DECIMAL value and it accepts the larger number without an error.

If you are going to test whether or not the string is numeric you need to do the replacements first. In your original query your WHERE clause is filtering on the original varchar values before you've done the replacements or checked for valid numbers.


SELECT
ID
,CAST(Result AS DECIMAL(20,2)) AS Result
FROM
(
SELECT
ID
,REPLACE(Col1,',','') AS Result
,(CASE
WHEN NULLIF(Col1,'') IS NULL
THEN 0
WHEN ISNUMERIC(ISNULL(NULLIF(REPLACE(Col1,',',''),''),0)) = 1
THEN 1
ELSE 0
END) AS isNum
FROM
#TempTable
) r
WHERE
IsNum = 1


 
Post #1419661
Posted Wednesday, February 13, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
Part of the issue may be using ISNUMERIC. This function is barely passable as a validation or filtering tool. Take the fine example that Steve posted and add 1 more row to the insert.

SELECT '123e4'

Like this to see the whole picture.

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Col1] VARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT '123456789'
UNION
SELECT '123456789123456789'
UNION
SELECT '123,456,789,123,456,789'
UNION
SELECT NULL
UNION
SELECT ''
UNION
SELECT 'XYZ'
union
SELECT '123e4'

--SELECT * FROM #TempTable

SELECT
ID
,CAST(Result AS DECIMAL(20,2)) AS Result
FROM
(
SELECT
ID
,REPLACE(Col1,',','') AS Result
,(CASE
WHEN NULLIF(Col1,'') IS NULL
THEN 0
WHEN ISNUMERIC(ISNULL(NULLIF(REPLACE(Col1,',',''),''),0)) = 1
THEN 1
ELSE 0
END) AS isNum
FROM
#TempTable
) r
WHERE
IsNum = 1

The last value will pass the ISNUMERIC check but it still can't be cast as a decimal.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419708
Posted Wednesday, February 13, 2013 9:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 19, 2014 9:02 PM
Points: 82, Visits: 328
Hi

Thanks to all, This issue rectified by using PATINDEX(%[0-9]%) in Where clause.

--Ragu Thangavel
Post #1419805
Posted Thursday, February 14, 2013 8:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
raguyazhin (2/13/2013)
Hi

Thanks to all, This issue rectified by using PATINDEX(%[0-9]%) in Where clause.

--Ragu Thangavel
Ragu,

I'm trying to understand how you used PATINDEX() to test strings for all digits. What you posted:
 PATINDEX(%[0-9]%)

. . . is syntactically incomplete as PATINDEX() requires two parameters and the pattern has to resolve to a string.
Even after changing it to something that will run, it seems that your wild-card pattern will merely check that there is at least one digit 0-9 in the expression/column to be tested, and not that it is ALL digits.

So, how does your WHERE clause use PATINDEX() to validate a column?

---
edit: minor typo: "use", not "us" in last question.
Post #1420365
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse