Remove the last character in a string in T-SQL?
SqlSql ServerTsqlSql Problem Overview
How do I remove the last character in a string in T-SQL
?
For example:
'TEST STRING'
to return:
'TEST STRIN'
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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, ''))
Solution 3 - Sql
Try this:
select substring('test string', 1, (len('test string') - 1))
Solution 4 - Sql
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.
Solution 5 - Sql
This will work even when source text/var is null or empty:
SELECT REVERSE(SUBSTRING(REVERSE(@a), 2, 9999))
Solution 6 - Sql
select left('TEST STRING', len('TEST STRING')-1)
Solution 7 - Sql
@result = substring(@result, 1, (LEN(@result)-1))
Solution 8 - Sql
If your coloumn is text
and not varchar
, then you can use this:
SELECT SUBSTRING(@String, 1, NULLIF(DATALENGTH(@String)-1,-1))
Solution 9 - Sql
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
Solution 10 - Sql
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
Solution 11 - Sql
I can suggest this -hack- ;).
select
left(txt, abs(len(txt + ',') - 2))
from
t;
Solution 12 - Sql
Get the last character
Right(@string, len(@String) - (len(@String) - 1))
Solution 13 - Sql
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
Solution 14 - Sql
Try this
DECLARE @String VARCHAR(100)
SET @String = 'TEST STRING'
SELECT LEFT(@String, LEN(@String) - 1) AS MyTrimmedColumn
Solution 15 - Sql
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
Solution 16 - Sql
declare @string varchar(20)= 'TEST STRING'
Select left(@string, len(@string)-1) as Tada
output:
Tada
--------------------
TEST STRIN
Solution 17 - Sql
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
Solution 18 - Sql
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
Solution 19 - Sql
Try It :
DECLARE @String NVARCHAR(100)
SET @String = '12354851'
SELECT LEFT(@String, NULLIF(LEN(@String)-1,-1))
Solution 20 - Sql
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
Solution 21 - Sql
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
Solution 22 - Sql
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)