January 23, 2010 at 7:43 am
how to convert (10,000) a varchar data type value to numeric ?
January 23, 2010 at 7:48 am
CAST or CONVERT. See Books Online (the SQL help file) for details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2010 at 7:50 am
how to convert comma seperated string to numeric ?
January 23, 2010 at 8:03 am
Use REPLACE to remove the commas, then use CAST or CONVERT. Alternatively, you could cast first to the Money data type (providing it has sufficient precision) and then cast to numeric.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2010 at 12:30 pm
First of all you need to make sure to know what that value actually represents:
It could be either 10000 or 10.
Based on that you'd have to replace the comma with an empty string or with a '.' (dot).
February 22, 2012 at 10:24 am
I am attempting to convert a varchar to numeric. However, I am trying to convert just a substring of the varchar.
I know with 100% certainty that this query only returns numeric info. However, it won't let me convert the substring. (I tried cast, too.) I've consulted books online. I am just not sure whether my using substring is possible.
SELECT SUBSTRING(Col4,1,CHARINDEX('%',Col4)-1)
FROM Executive.StagedimWeight
WHERE Col4 IS NOT NULL
AND Col4 LIKE '%\%%'ESCAPE '\'
The above query returns:
35
65
However, if I try this convert syntax:
SELECT CONVERT(NUMERIC(5,4),SUBSTRING(Col4,1,CHARINDEX('%',Col4)-1))
FROM Executive.StagedimWeight
WHERE Col4 IS NOT NULL
AND Col4 LIKE '%\%%'ESCAPE '\'
I get: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
Any help greatly appreciated!
February 22, 2012 at 10:30 am
You have some data in that column that does not convert correctly. That's the overflow.
No matter what you think, this is a common error and a reason why you store numeric data in numeric data types. There is some value in your column which is not converting,
February 22, 2012 at 10:31 am
Finally got it! I'm not sure if this is the best way, but it worked for me:
select CONVERT(NUMERIC(3,0),(CONVERT(int,SUBSTRING(Col4,1,CHARINDEX('%',Col4)-1))))/100
FROM Executive.StagedimWeight
WHERE Col4 IS NOT NULL
AND Col4 LIKE '%\%%'ESCAPE '\'
February 22, 2012 at 4:42 pm
The problem with the original query is that it was trying to convert to a NUMERIC(5,4), which will hold a maximum value of 9.9999. The value "35" has two digits left of the decimal, but NUMERIC(5,4) allows only one. Therefore, an overflow resulted. The query that worked (above post) changed the CONVERT to use NUMERIC(3,0), resulting in sufficient space to hold a two-digit number. I suggest you use a NUMERIC value large enough to hold all the expected values. For example, if you want to hold 10,000 as listed in the original post, you will need a NUMERIC(5,0), NUMERIC(6,1), or something where the first digit of the NUMERIC definition minus the second digit is 5 or greater (5-0=5; 6-1=5, etc).
February 23, 2012 at 5:53 am
Hi
select convert(bigint,REPLACE ('10,00,000',',',''))
Siva Kumar J
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply