Remove Leading Zeros using PATINDEX

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 1071
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.


Comments or Responses

Login to post response