Converting a "string" to a number

[Explorer5]

How do i convert a string (which is really a number, but just saved in the DB as 'text') into a number that can be added and subtracted?

[byron1021]

This would be for MS SQL, but there is probably similar syntax in MySQL and
Access select cast(col1 as int) + col2 + cast(col3 as numeric(9,2) from table
It gets a little stick for MS SQL 'text' datatypes (not varchar or char), you
would need to cast the text data type to a varchar first, then cast to a int,
numeric, etc. Also when adding int's and numeric, look out for rounding
issues. int + numeric I believe will round to an int, you would need to cast
both as numeric to get a floating number back

[paross1]

With Access, you can use CInt() or CLng() functions to convert a string to integer or LONG, whichever is applicable.

SELECT CInt(col_name) AS num_col
FROM whatever
WHERE stuff

Phil

[kyle969]

For Oracle use this: SELECT NVL( TO_NUMBER(col_txt), 0) * amt_nbr This
converts any nulls to 0. If you are certain there are no nulls, just use the
to_number(). There are other functions to convert to different datatypes, but
this wil work for most applications.