SQL Express - Changing a Character or String Field to number

If all data in this field is number, it's better to change datatype to appropriate number type like int,bigint or float

this can change datatype of col1 from table1 to int for example:


ALTER TABLE table1 ALTER COLUMN col1 int



but remember data must be correct for converting

Thank you - When you say data must be correct for converting - what do you mean?


**for example - Col 1 contain the fuel tax on a product, when it is pushed to SQL it will come across as a character if I use the ALTER TABLE, ALTER COLUMN COL1 Int will that change all data in the column to be a number?

Thanks.

You can do something like this:

Add a new column to the table with datatype of int or decimal using Management Studio

Then update the new column with values using a query like this:

Update Table set NewColumn = convert(int, OldColumn)
WHERE isnumeric(OldColumn) = 1

Then you can see if any items could not be converted and manually make changes/update values.

Once you are happy, delete OldColumn and rename NewColumn to OldColumn using Management Studio.

I mean that if you have no digit characters in this field , then you can not alter datatype to int directly
but if you have only numeric values then converting types is possible simply. just do it

The problem I have now is I changed the Column to Money or Decimal or Numeric - when I go to use the column in a formula or bring the view into Crystal reports it still acts as if the column is Text .....  

Any ideas on what is going on?

Thanks.

can you remove this column from crystal report and add it again? refreshing database table in crystal will help too

In Crystal reports you need to select "Verify Database" on the database menu