February 13, 2013 at 2:48 am
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
February 13, 2013 at 2:58 am
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
February 13, 2013 at 4:41 am
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
February 13, 2013 at 4:45 am
Please provide examples of values which will and will not convert from your table
February 13, 2013 at 5:09 am
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 <> ''
February 13, 2013 at 8:28 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2013 at 11:06 am
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
February 13, 2013 at 12:45 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2013 at 9:24 pm
Hi
Thanks to all, This issue rectified by using PATINDEX(%[0-9]%) in Where clause.
--Ragu Thangavel
February 14, 2013 at 8:23 pm
raguyazhin (2/13/2013)
HiThanks 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.
February 14, 2013 at 8:43 pm
Went looking on the net and found an answer right here in SSC (of course!!). If you want to test for all digits, the expression NOT LIKE '%[^0-9]%' should do it. Do read the entire post from Jeff Moden about testing for numbers.
February 14, 2013 at 9:50 pm
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0
I am just mentioned a name only.
February 14, 2013 at 9:53 pm
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0
February 14, 2013 at 10:15 pm
raguyazhin (2/14/2013)
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0
The key, then, is the Not-to-match operator, "^". Zero returned means PATINDEX() found no characters other than digits, 0-9. Got it! Thanks for the clarification.
May 7, 2013 at 12:09 pm
In your query you are casting as decimal(18,2). Are your numbers always going to be whole numbers?
The following example
select PATINDEX('%[^0-9]%', '1234234.20')
does not return zero.
----------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply