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

Question

How do I remove the last character in a string in T-SQL?

For example:

'TEST STRING'

to return:

'TEST STRIN'

Answer 1

Perhaps I missed it above, but none of the answers above seem to verify that you actually want to remove the last character. Though some account for NULL or empty strings, they all just assume the comma, or whatever char you are removing, is present in all cases.

DECLARE @test VARCHAR(20)  = 'CA,NV,TN,FL,'  -- 'CA,NV,TN,FL'

SELECT 
    CASE WHEN CHARINDEX(',',REVERSE(@test)) = 1 
         THEN SUBSTRING(@test, 1, len(@test) - 1)
         ELSE @test
    END AS Fixed

Answer 2

Try this:

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

Answer 3

declare @string varchar(1000) = null
select @string =  isnull(substring (@string, 1, nullif(len(@string)-1,-1)), '')
select @string 
go

declare @string varchar(1000) = ''
select @string =  isnull(substring (@string, 1, nullif(len(@string)-1,-1)), '')
select @string 
go

declare @string varchar(1000) = '123,'
select @string =  isnull(substring (@string, 1, nullif(len(@string)-1,-1)), '')
select @string 

Answer 4

Another approach, if your string @s is of a certain max size, say 10 characters.

select trim(substring(right(replicate(' ', 10) + coalesce(@s,''), 10), 1, 9))

This approach was useful when converting GTIN numbers (10 - 14 digits with a final check digit and leading 0s) to a UPC with no check digit or leading 0s:

cast(cast(SUBSTRING(RIGHT('00000' + @gtin, 14), 1, 13) as bigint) as varchar)

Examples:

• 0012345235 --> 1234523
• 12345678901234 --> 1234567890123

Answer 5

I encountered this problem and this way my problem was solved:

Declare @name as varchar(30)='TEST STRING'  
Select left(@name, len(@name)-1) as AfterRemoveLastCharacter 

Answer 6

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

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

Answer 7

This is quite late, but interestingly never mentioned yet.

select stuff(x,len(x),1,'')

ie:

take a string x
go to its last character
remove one character
add nothing

Answer 8

I can suggest this -hack- ;).

select 
    left(txt, abs(len(txt + ',') - 2))
from 
    t;

SQL Server Fiddle Demo

Answer 9

Get the last character

Right(@string, len(@String) - (len(@String) - 1))

Answer 10

@result = substring(@result, 1, (LEN(@result)-1))

Answer 11

Try this,

DECLARE @name NVARCHAR(MAX) SET @name='xxxxTHAMIZHMANI****'SELECT Substring(@name, 5, (len(@name)-8)) as UserNames

And the output will be like, THAMIZHMANI

Answer 12

Try this

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

Answer 13

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

Answer 14

select left('TEST STRING', len('TEST STRING')-1)

Answer 15

declare @string varchar(20)= 'TEST STRING'
Select left(@string, len(@string)-1) as Tada

output:

Tada
--------------------
TEST STRIN

Answer 16

Try It :

  DECLARE @String NVARCHAR(100)
    SET @String = '12354851'
    SELECT LEFT(@String, NULLIF(LEN(@String)-1,-1))

Answer 17

To update the record by trimming the last N characters of a particular column:

UPDATE tablename SET columnName = LEFT(columnName , LEN(columnName )-N) where clause

Answer 18

My answer is similar to the accepted answer, but it also check for Null and Empty String.

DECLARE @String VARCHAR(100)

SET @String = 'asdfsdf1'

-- If string is null return null, else if string is empty return as it is, else 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

Answer 19

you can create function

CREATE FUNCTION [dbo].[TRUNCRIGHT] (@string NVARCHAR(max), @len int = 1)
RETURNS NVARCHAR(max)
AS
BEGIN
    IF LEN(@string)<@len
        RETURN ''
    RETURN LEFT(@string, LEN(@string) - @len)
END

Answer 20

declare @x varchar(20),@y varchar(20)
select @x='sam'
select 
case when @x is null then @y
      when @y is null then @x
      else @x+','+@y
end


go

declare @x varchar(20),@y varchar(20)
select @x='sam'
--,@y='john'
DECLARE @listStr VARCHAR(MAX)   

SELECT @listStr = COALESCE(@x + ', ' ,'') +coalesce(@y+',','')
SELECT left(@listStr,len(@listStr)-1)

Answer 21

If your coloumn is text and not varchar, then you can use this:

SELECT SUBSTRING(@String, 1, NULLIF(DATALENGTH(@String)-1,-1))

Answer 22

If you want to do this in two steps, rather than the three of REVERSE-STUFF-REVERSE, you can have your list separator be one or two spaces. Then use RTRIM to trim the trailing spaces, and REPLACE to replace the double spaces with ','

select REPLACE(RTRIM('a  b  c  d  '),'  ', ', ')

However, this is not a good idea if your original string can contain internal spaces.

Not sure about performance. Each REVERSE creates a new copy of the string, but STUFF is a third faster than REPLACE.

also see this

Answer 23

I love @bill-hoenig 's answer; however, I was using a subquery and I got caught up because the REVERSE function needed two sets of parentheses. Took me a while to figure that one out!

SELECT
   -- Return comma delimited list of all payment reasons for this Visit
   REVERSE(STUFF(REVERSE((
        SELECT DISTINCT
               CAST(CONVERT(varchar, r1.CodeID) + ' - ' + c.Name + ', ' AS VARCHAR(MAX))
          FROM VisitReason r1
          LEFT JOIN ReasonCode c        ON c.ID = r1.ReasonCodeID
         WHERE p.ID = r1.PaymentID
         FOR XML PATH('')
              )), 1, 2, ''))                        ReasonCode
  FROM Payments p

Answer 24

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.

Answer 25

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, ''))