[X]

T-SQL Remove trailing zeros from left

T-SQL - Removing trailing zeroes

There are some cases when we have a value with trailing zeors to the left like '00089' but actually we want to consider only '89'.

The one solution to above problem is we can CAST it as INT. But since the value can be non-numeric also like '00089 MM:NN'. In this case CAST to INT will not work.

We will try to solve this problem using PATINDEX.

Below is the Code:

 

 

--T-SQL Remove trailing zeros from left.
	
	--let's have a variable to hold value with trailing zeros
	DECLARE @value VARCHAR(MAX)='00000800909090009'
	
	SELECT 
	CASE 
	WHEN REPLACE(@value, '0','')=''
	THEN '0' 
	ELSE 
	SUBSTRING(@value, PATINDEX('%[^0]%',@value), LEN(@value) - PATINDEX('%[^0]%',@value) + 1) 
	END AS Value_WithoutTrailingZeros
	
	SET @value='000'
	
	SELECT 
	CASE 
	WHEN REPLACE(@value, '0','')=''
	THEN '0' 
	ELSE 
	SUBSTRING(@value, PATINDEX('%[^0]%',@value), LEN(@value) - PATINDEX('%[^0]%',@value) + 1) 
	END AS Value_WithoutTrailingZeros

 

And the output is like:

blog comments powered by Disqus

Posts By Month