SQL update column from varchar data type to int

Question

I imported a txt file into my table column B which is of datatype varchar the data is like 10.00 GB, 20 TB , 100 MB etc

column a     column b
host A       100 TB
host B       20 GB
host C       100 MB

I did try convert (int,column name) which returned error advising cannot convert data type varchar to type int

I can replace the GB with blanks but want to convert anything with Tb or MB to converted to GB. yes I don't want the TB or GB or MB to be displayed in my column B.just numbers.

may be good if i can store these values in a separate column with datatype as int and then delete my original column in the same table.

Please could someone help

Answer 1

You can split the column using:

select t.a, t.b,
       cast(left(b, charindex(' ', b)) as int) as num,
       right(b, 2) as units
from t;

This assumes that the values are all in exactly this format.

You can put it in one column by using multiplication:

select t.a, t.b,
       (cast(left(b, charindex(' ', b)) as float) *
        (case right(b, 2) when 'GB' then 1.0
                            when 'TB' then 1024.0
                            when 'MB' then 1.0/1024
                            else 1.0
         end)) as inGB
from t;

EDIT:

You might have a problem if all the values are not in the format you expect. You can you a case statement, something like:

case when b like '[0-9] %' or b like '[0-9][0-9] %' or b like '[0-9][0-9][0-9] %' then . . .

to verify that a number is actually at the beginning of the string.