SQL Server Portal

‘How to remove the last character in a string’ is a general problem that we usually face while developing a dynamic SQL string or sometimes due to legacy data etc.

Given below are multiple solutions to remove the last character from a string.

SOLUTION 1 : Using LEFT string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,LEFT(@String,DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT

SOLUTION 2 : Using STUFF string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,STUFF(@String,DATALENGTH(@String), 1, '')
As [Last Character removed from string]
GO
--OUTPUT

SOLUTION 3 : Using SUBSTRING string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,SUBSTRING(@String,1, DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT