You should also evaluate what nvl(substr(weight - trunc(weight), 2), 0) is doing...once you understand that then you can determine what functions in T-SQL will return the same results.
Looking at this - you have a weight in decimal format and you want to break that out to separate pieces. The easiest way to get the value left of the decimal is to convert to integer - to get the value to the right of the decimal depends on how you want the result.
For example - if you want gms to be 0.nnn then you can use: weight % 1
If you want an integer value for gms - you can use the precision of weight (for example - decimal(5,2) - you can then use: cast(weight % 1 * 100 as int)
There are other options...depends on the results you want.