Convert Char to numeric

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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

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

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.

Chris LuttrellSenior Database ArchitectCommented:
What is the soulution you "found" and how is it different from something presented here?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.