Remove the last character in a string in T-SQL?

Question

e.g.

DECLARE @String VARCHAR(100)
SET @String = 'TEST STRING'

-- Chop off the end character
SET @String = 
     CASE @String WHEN null THEN null 
     ELSE (
         CASE LEN(@String) WHEN 0 THEN @String 
            ELSE LEFT(@String, LEN(@String) - 1) 
         END 
     ) END


SELECT @String

If for some reason your column logic is complex (case when ... then ... else ... end), then the above solutions causes you to have to repeat the same logic in the len() function. Duplicating the same logic becomes a mess. If this is the case then this is a solution worth noting. This example gets rid of the last unwanted comma. I finally found a use for the REVERSE function.

select reverse(stuff(reverse('a,b,c,d,'), 1, 1, ''))

Try this:

select substring('test string', 1, (len('test string') - 1))

If your string is empty,

DECLARE @String VARCHAR(100)
SET @String = ''
SELECT LEFT(@String, LEN(@String) - 1)

then this code will cause error message 'Invalid length parameter passed to the substring function.'

You can handle it this way:

SELECT LEFT(@String, NULLIF(LEN(@String)-1,-1))

It will always return result, and NULL in case of empty string.

This will work even when source text/var is null or empty:

SELECT REVERSE(SUBSTRING(REVERSE(@a), 2, 9999))