• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1071
  • Last Modified:

SQL SSIS/TSQL convert table column from varchar to numeric?

Hello,

I have a table that stores all of my columns as "varchar(150)".

Some of the columns in the table only contain money values and I would like to convert them to such (numeric datatype is best for money?).

Here is how the table looks like:

table-varchar.JPG
The table has about 1.6m rows, how can I using TSQL or SSIS efficiently convert the three columns from varchar to a more appropriate datatype for money?

Please let me know, I am looking for a descriptive example of how to make this work.

Thanks.
0
mirde
Asked:
mirde
1 Solution
 
Patrick MatthewsCommented:
Actually, IMHO the best data type to use for money is...the money data type!  (Yes, there is one.)

Anyway...

SELECT CONVERT(money, LTRIM(RTRIM([TF FOB]))) AS [TF FOB]
FROM SomeTable
WHERE ISNUMERIC(LTRIM(RTRIM([TF FOB]))) = 1

Open in new window

0
 
mirdeAuthor Commented:
Thanks that worked.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now