Solved

Convert Char to numeric

Posted on 2009-05-04
7
869 Views
Last Modified: 2013-11-15
I have a char field that stores a numeric value in it 128,074.  I want to update this value in a stored procedure by multiplying by .015. So far I am unable to convert to numeric. I receive this error Error converting data type varchar to numeric.
0
Comment
Question by:NCLGS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:rem1010
ID: 24296222
I have attached a very simple code snippet using Perl. However, the concept in most languages is similar.
First, convert the field to numeric like this Perl builtin function, which will result in ONLY numbers, no commas, no decimal points, no spaces, just 0-9
$Numbfield =~ s/\D+//g;
If you need to have decimal points and negative signs, then perl has a substitution such as:
$Numbfield =~ s/[0-9\.\,\-]//g;
The backslashes in perl are used to escape the value so that a "," is meant to be a "," and not a field delimiter, etc.

Since you did not specify what platform, language, or database, I utilized Perl which is cross platform.

#!/usr/bin/perl -w
my $Textfield = qq[128,074];;
my $Numbfield;
my $Answer;
my $Multiplier = '.015';
$Numbfield = $Textfield;
print qq[numbfield is $Numbfield\n];
$Numbfield =~ s/\D+//g;
print qq[numbfiled num is $Numbfield\n];
print qq[multi is $Multiplier\n];
$Answer = $Numbfield * $Multiplier;
 
print qq[answer is  $Answer\n];

Open in new window

0
 
LVL 3

Expert Comment

by:rem1010
ID: 24296313
Sorry, missed your "Stored Procedure" statement.
A simple simple solution is to perform a function on that data WHEN it is stored into the database and store the ACTUAL value in both the Varchar and an INT field, if the varchar field is required.
Sure it is not ultra dramatic, but it would solve the problem in a quick and inexpensive timely manner.
Take a look at processing that value WHEN it is acquired rather than AFTER.

0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24563793
The problem is the implicit conversion of a varchar/char into a numeric will not handle the commas in the string.  You have to remove them either with Replace or the Money conversion has a style to do it.
The code snippet below shows the two options for converting your string value of '128,074' into a numeric value to use in your equation.
DECLARE @v VARCHAR(50), @multiplier NUMERIC(18,4);
SET @v = '128,074';
SET @multiplier = 0.15;
 
SELECT @v, @multiplier, CONVERT(NUMERIC(18,4),CONVERT(money,@v,1))*@multiplier AS Answer
 
SELECT @v, @multiplier, CONVERT(NUMERIC(18,4),REPLACE(@v,',',''))*@multiplier AS Answer

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 25225093
What is the soulution you "found" and how is it different from something presented here?
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question