SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Varchar to Decimal


Convert Varchar to Decimal

Author
Message
raguyazhin
raguyazhin
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 357
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
sven.treffinger
sven.treffinger
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 222
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
raguyazhin
raguyazhin
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 357
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
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25794 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


raguyazhin
raguyazhin
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 357
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 <> ''
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65464 Visits: 17980
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.

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)
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2155 Visits: 1721
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




 
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65464 Visits: 17980
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.

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)
raguyazhin
raguyazhin
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 357
Hi

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

--Ragu Thangavel
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 3059
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search