Remove Leading Zeros and decimals using PATINDEX

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

Comments or Responses

Login to post response