删除T-SQL字符串中的最后一个字符?

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

如何在T-SQL中删除字符串中的最后一个字符?

For example:

例如:

'TEST STRING'

to return:

返回:

'TEST STRIN'

19 个解决方案

#1

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

#2

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.

如果出于某种原因，您的列逻辑是复杂的(当…然后……其他的……结束)，然后上面的解决方案导致您必须在len()函数中重复相同的逻辑。重复同样的逻辑会变得一团糟。如果是这种情况，那么这是一个值得注意的解决方案。这个例子去掉了最后一个不需要的逗号。我终于找到了反向函数的用途。

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

#3

Try this:

试试这个:

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

#4

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.

它将始终返回结果，如果是空字符串，则返回NULL。

#5

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

如果您想要在两个步骤中完成此操作，而不是在三个步骤中执行反向事务，您可以将列表分隔符设置为一个或两个空格。然后使用RTRIM修整尾随空间，并用'，'

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

也看到这

#6

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

如果你的coloumn是文本，而不是varchar，那么你可以这样使用:

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

#7

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

#8

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

#9

I can suggest this -hack- ;).

我可以建议这个-hack-;

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

SQL Server Fiddle Demo

SQL Server小提琴演示

#10

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

#11

Get the last character Right(@string, len(@String) - (len(@String) - 1))

获取最后一个字符(@string, len(@string) - (len(@string) - 1)

#12

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

#13

Try this

试试这个

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

#14

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!

我喜欢@bill-hoenig的回答;但是，我正在使用子查询，我被捕获了，因为反向函数需要两组圆括号。我花了一段时间才弄明白这一点!

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

#15

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

通过修剪某一列的最后N个字符来更新记录:

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

#16

Try It :

试一试:

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

#17

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

output:

输出:

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

#18

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

输出将会是，THAMIZHMANI。

#19

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)