Converting varchar to numeric

[colby]

I am having Dificulty explicitly converting a varchar to numeric
The table 'cost' has a 'value' column set to varchar
the user is allowed to imput commas in the value.
When I run my report to extract the cost values
and try to convert or cast so that I can do sum arithmetic
on it. It falls over

Thanks in advance for any help?
Dave

 

[LFCfan]

You could try something like this

set numeric_field = CONVERT(NUMERIC(8,2),REPLACE(varchar_field,',',''))

The REPLACE will replace the comma, if there is one, with an empty space, so '12,345.67' will become '12345.67', which can then be converted to numeric

Hope this helps

 

[colby]

Thanks again works fine

 

[SQLSister]

Clby, I'm sure from the way you wrote this that you are working with a database that is well past the design stages and so changing the way you do this is probably impractical. But for those reading the boards who are learning how to handle different problems so they can create better software, I propose that the initial design of this application is flawed. It is good to allow users to type in a way that is natural for them including putting in the commas. However, it would be better to strip this out at the time the data is sent to the database and use a numeric type. This way the the user is happy because he types the data the way he wants to, the programmers have less work to do because they don't have to convert the field every time they want to do a calculation on it and consistent data is in the database making it easier to format on the user interface for consistent display. It also makes it better for instituing business rules on the data and it reduces the chances of input error because a numeric field will not accept nonnumermic data. This same thing is true when people don't define dates as a date field, then the data is generally not validated to be a correct date and when you try to use the data as such you find you hve to fix bad records such as February 30, 2002 or ASAP instead of a date. In designing a database it is better to assign the data type that is correct for the type of information you need to have. Thus any date information should always be a data field and any numeric data that will require calculations should be some type of numeric field. Numbers which will never be used for calculation such as phone numbers can be stored as char types, but even then, I would strip out the nonnumeric characters people type and store onl y the numbers to make the display of telephone numbers consistent and to make it easier to validate that they include the correct number of digits.