Remove Leading Zeros using PATINDEX

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