SQL SERVER – Remove the last character in a string

Sometimes it is required to remove last character usually a comma from SQL string, below are 3 way to achieve it.

Option 1: Using LEFT function

DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'
 
SELECT
@String As [String]
,LEFT(@String,LEN(@String)-1)
As [Last Character removed from string]
GO

Option 2: Using STUFF function

DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'
 
SELECT
@String As [String]
,STUFF(@String,LEN(@String), 1, '')
As [Last Character removed from string]
GO

Option 3: Using SUBSTRING function

DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'
 
SELECT
@String As [String]
,SUBSTRING(@String,1, LEN(@String)-1)
As [Last Character removed from string]
GO