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

convert varchar to numeric Expand / Collapse
Author
Message
Posted Saturday, January 23, 2010 7:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107
how to convert (10,000) a varchar data type value to numeric ?
Post #852533
Posted Saturday, January 23, 2010 7:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 40,184, Visits: 36,591
CAST or CONVERT. See Books Online (the SQL help file) for details.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #852535
Posted Saturday, January 23, 2010 7:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107
how to convert comma seperated string to numeric ?
Post #852536
Posted Saturday, January 23, 2010 8:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 40,184, Visits: 36,591
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #852538
Posted Saturday, January 23, 2010 12:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 6,841, Visits: 13,360
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).




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #852589
Posted Wednesday, February 22, 2012 10:24 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, October 13, 2014 9:25 AM
Points: 126, Visits: 323
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!
Post #1256134
Posted Wednesday, February 22, 2012 10:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
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,







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1256138
Posted Wednesday, February 22, 2012 10:31 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, October 13, 2014 9:25 AM
Points: 126, Visits: 323
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 '\'
Post #1256139
Posted Wednesday, February 22, 2012 4:42 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:46 PM
Points: 106, Visits: 369
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).
Post #1256346
Posted Thursday, February 23, 2012 5:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:22 AM
Points: 60, Visits: 520
Hi

select convert(bigint,REPLACE ('10,00,000',',',''))

Siva Kumar J
Post #1256579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse