Converting measurements into single unit

1

I have a .txt file that I've exported from an external table, this text file, when pasted into an excel file has a list of measurements, basically the fields are:
Part Number|Manufarcturer Number|Dimensions|Height|Length|Width|Unit

Now what I'm trying to do with this data is to use SQL to take the height, length and width, and then depending on the unit of measurement (These are all in mm or in) I would work out the total cubic inches. I literally have no idea on how to go about this so I wondered if anybody could offer any solutions and maybe give me an example of some code that will accomplish this task.

2

There are 25.4 mm in an inch, so something like this?!:

SELECT [Part Number], ..., 
    CAST(CASE WHEN is_in_inches = 1 THEN Dimensions ELSE Dimensions / 25.4 END AS decimal(9, 2)) AS Dimensions,
    CAST(CASE WHEN is_in_inches = 1 THEN Height ELSE Height / 25.4 END AS decimal(9, 2)) AS Height,
    ...
FROM 
CROSS APPLY (
    SELECT CASE WHEN Unit IN ('In', 'Inch') THEN 1 ELSE 0 END AS is_in_inches
) AS ca1