In this case the objective is to remove Leading Zeros and DOTS using PATINDEX
DECLARE @somevalue VARCHAR(50) = '00001234590789005.9005'; --set the initial value
DECLARE @dotPosition INT -- variable to store the occurance of dot position
DECLARE @nonZeroPosition INT -- variable to store the occurance of first non zero value position
DECLARE @valueBeforeDecimal VARCHAR(50) -- variable to store values before decimals
SELECT @dotPosition = PATINDEX('%[[.]%',@somevalue)
SELECT @valueBeforeDecimal = LEFT(@somevalue,@dotPosition-1) --Extract non-decimal part
SELECT @nonZeroPosition = PATINDEX('%[^0]%',@valueBeforeDecimal)
SELECT ExtractedValue = SUBSTRING(@valueBeforeDecimal,@nonZeroPosition,(LEN(@valueBeforeDecimal)-(@nonZeroPosition - 1))) --remove leading zero(s)
In this case, we have first extracted the non-decimal values and then from that we have removed the Leading Zeros.
Result ExtractedValue
----------------------
1234590789005