SQL Express - Changing a Character or String Field to number

Erin Leale
Erin Leale used Ask the Experts™
on
I'm new to SQL - however - we push tables from a MAS data base.  The fields in the table are all string (character) fields, we need some of these fields that are numbers to be formatted in our SQL table as a number.  What is the best way to accomplish this task?  

Usually using these fields in Crystal Reports we would use the tonumber function.

Thank you for your help!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

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

Reza RadConsultant, Trainer

Commented:
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
Erin LealeConsultant/Controller

Author

Commented:
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.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
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.
Reza RadConsultant, Trainer

Commented:
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
Erin LealeConsultant/Controller

Author

Commented:
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.
Reza RadConsultant, Trainer

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

Commented:
In Crystal reports you need to select "Verify Database" on the database menu
Consultant/Controller
Commented:
Even in the View in SQL it still treats the fields as if they are text.  

I created a new Crystal report and used the new view - same result.

?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial