Best way in SQL Server 2012 to parse a fixed-width string?

Question

I work with data from an older system, which has a common transaction table with a data column that contains various types of records, and where the decimal date is not included.

For example, the following line has a price of 358.25 premium 0 (00.00) and tons 003.382, so the correct gross cost is 1104.1265

358250000003082-

My T-SQL is to extract this (ignore the substring values, the actual data column is much larger than my example)

convert(decimal(6,0), SUBSTRING(data,105,6))/1000 * (convert(decimal(5,0), SUBSTRING(data,51,5))/100 + convert(decimal(4,0), SUBSTRING(data,56,4)/100)) grossPrice

This works pretty well, but I need to do it in many places, and if I get the brackets in the wrong place, it works fine, but gives the wrong result.

Is there a better way to convert a string to decimal where the number of decimal places is implied?

I was hoping for something like this convert(decimal(6,3), '003082'), but this generates an overflow error (understandable)

Answer 1

You can easily convert your example. You just have to consider the math of integers. You will notice that I am divided by 1000. This causes an implicit conversion, so you get the correct decimal places.

select cast(convert(int, '003082') / 1000. as numeric(6,3))