Technical Article

Remove Leading Zeros in any situation T-SQL

,

This script will remove all leading zeros from a string and shows actual value. This also handles if the string starts with the text and still does have leading zeros in between.

--Example 1: - Starts with Zero
--=========
declare @String varchar(30)
select @String = '00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end
GO

--Example 2: - Starts with Text but still has leading zeros
--=========
declare @String varchar(30)
select @String = 'TEST00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end

Rate

Share

Share

Rate