In this scenario, our objective is to remove the Leading Zeros from a string using PATINDEX
DECLARE @somevalue VARCHAR(50) = '00001234590789005'; --set the initial value
DECLARE @nonZeroPosition INT -- variable to store the occurance of first non zero value position
SELECT @nonZeroPosition = PATINDEX('%[^0]%',@somevalue) -- This pattern ensures the search position should encounter a non-zero value in the entire string
SELECT RemoveLeadingZeros = SUBSTRING(@somevalue,@nonZeroPosition,(LEN(@somevalue)-(@nonZeroPosition - 1))) --Extract the non-zero values.
RemoveLeadingZeros
----------------------
1234590789005