Convert Varchar to Decimal

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Convert Varchar to Decimal

15 posts, Page 1 of 2

1 2 »»

Convert Varchar to Decimal

Rate Topic

Display Mode

Topic Options

Author

Message

raguyazhin

Posted Wednesday, February 13, 2013 2:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 15, 2013 12:39 AM
Points: 80, Visits: 312

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

sven.treffinger

Posted Wednesday, February 13, 2013 2:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:44 AM
Points: 41, Visits: 157

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', ',','.'))

Post #1419362

raguyazhin

Posted Wednesday, February 13, 2013 4:41 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 15, 2013 12:39 AM
Points: 80, Visits: 312

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

anthony.green

Posted Wednesday, February 13, 2013 4:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

raguyazhin

Posted Wednesday, February 13, 2013 5:09 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 15, 2013 12:39 AM
Points: 80, Visits: 312

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

Sean Lange

Posted Wednesday, February 13, 2013 8:28 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

raguyazhin (2/13/2013)

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

Post #1419563

Steven Willis

Posted Wednesday, February 13, 2013 11:06 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 283, Visits: 1,239

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

Sean Lange

Posted Wednesday, February 13, 2013 12:45 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1419708

raguyazhin

Posted Wednesday, February 13, 2013 9:24 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 15, 2013 12:39 AM
Points: 80, Visits: 312

Hi

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

--Ragu Thangavel

Post #1419805

john.arnott

Posted Thursday, February 14, 2013 8:23 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

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 »

15 posts, Page 1 of 2

1 2 »»

Permissions