blocks|key|1481388|text|例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481389|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|code-block|syntax|javascript|1481390|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

e.g.

<pre><code>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
</code></pre>

blocks|key|1266790|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1266791|select+substring('test+string',+1,+(len('test+string')+-+1))|code-block|syntax|javascript|1266792|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>select substring('test string', 1, (len('test string') - 1))
</code></pre>

blocks|key|1481443|text|如果由于某些原因，您的列逻辑很复杂(当...然后..。否则..。end)，则上面的解决方案会导致您必须在len()函数中重复相同的逻辑。复制相同的逻辑会变得一团糟。如果是这样，那么这是一个值得注意的解决方案。这个例子去掉了最后一个不需要的逗号。我终于找到了反向函数的用法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481444|select+reverse(stuff(reverse('a,b,c,d,'),+1,+1,+''))|code-block|syntax|javascript|1481445|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>select reverse(stuff(reverse('a,b,c,d,'), 1, 1, ''))
</code></pre>

blocks|key|1266912|text|如果字符串为空，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1266913|DECLARE+@String+VARCHAR(100)
SET+@String+=+''
SELECT+LEFT(@String,+LEN(@String)+-+1)|code-block|syntax|javascript|1266914|则此代码将导致错误消息“传递给substring函数的长度参数无效”。|1266915|你可以这样处理它：|1266916|SELECT+LEFT(@String,+NULLIF(LEN(@String)-1,-1))|1266917|它总是返回result，如果是空字符串，则返回NULL。|1266918|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If your string is empty, 

<pre><code>DECLARE @String VARCHAR(100)
SET @String = ''
SELECT LEFT(@String, LEN(@String) - 1)
</code></pre>

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

You can handle it this way:

<pre><code>SELECT LEFT(@String, NULLIF(LEN(@String)-1,-1))
</code></pre>

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

blocks|key|1481528|text|如果您的列是text而不是varchar，那么您可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1481529|SELECT+SUBSTRING(@String,+1,+NULLIF(DATALENGTH(@String)-1,-1))|code-block|syntax|javascript|1481530|entityMap^0|6|4|D|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

If your coloumn is <code>text</code> and not <code>varchar</code>, then you can use this:

<pre><code>SELECT SUBSTRING(@String, 1, NULLIF(DATALENGTH(@String)-1,-1))
</code></pre>

blocks|key|1267028|text|我喜欢@bill-hoenig的答案；但是，我使用子查询，因为反向函数需要两组括号，我花了一段时间才弄明白这个！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267029|SELECT
+++--+Return+comma+delimited+list+of+all+payment+reasons+for+this+Visit
+++REVERSE(STUFF(REVERSE((
++++++++SELECT+DISTINCT
+++++++++++++++CAST(CONVERT(varchar,+r1.CodeID)+%2B+'+-+'+%2B+c.Name+%2B+',+'+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|code-block|syntax|javascript|1267030|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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!

<pre><code>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
</code></pre>

blocks|key|1481667|text|如果你想用两个步骤来完成这件事，而不是三个反向的东西，你可以让你的列表分隔符是一个或两个空格。然后使用RTRIM修剪尾部空格，并使用REPLACE将双空格替换为'，‘|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481668|select+REPLACE(RTRIM('a++b++c++d++'),'++',+',+')|code-block|syntax|javascript|1481669|但是，如果原始字符串可以包含内部空格，则这不是一个好主意。|1481670|不确定性能。每次反向操作都会创建字符串的一个新副本，但是STUFF比REPLACE快三分之一。|1481671|另请参阅this|offset|length|1481672|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/5333613/fastest-way-to-update-varchar-column-with-text^0|0|0|0|0|4|4|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

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

<pre><code>select REPLACE(RTRIM('a b c d '),' ', ', ')
</code></pre>

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 <a href="https://stackoverflow.com/questions/5333613/fastest-way-to-update-varchar-column-with-text">this</a>

blocks|key|1267091|text|获取最后一个字符|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267092|Right(@string,+len(@String)+-+(len(@String)+-+1))|code-block|syntax|javascript|1267093|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Get the last character

<pre><code>Right(@string, len(@String) - (len(@String) - 1))
</code></pre>

blocks|key|1267095|text|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%2B','%2B@y
end


go

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

SELECT+@listStr+=+COALESCE(@x+%2B+',+'+,'')+%2Bcoalesce(@y%2B',','')
SELECT+left(@listStr,len(@listStr)-1)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1267096|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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)
</code></pre>

blocks|key|1267140|text|您可以创建函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267141|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|code-block|syntax|javascript|1267142|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can create function

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

blocks|key|1481813|text|我的答案与公认的答案类似，但它还会检查Null和空字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481814|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|code-block|syntax|javascript|1481815|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|1481836|text|要通过修剪特定列的最后N个字符来更新记录：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481837|UPDATE+tablename+SET+columnName+=+LEFT(columnName+,+LEN(columnName+)-N)+where+clause|code-block|syntax|javascript|1481838|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>UPDATE tablename SET columnName = LEFT(columnName , LEN(columnName )-N) where clause
</code></pre>

blocks|key|1481866|text|试试看：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481867|++DECLARE+@String+NVARCHAR(100)
++++SET+@String+=+'12354851'
++++SELECT+LEFT(@String,+NULLIF(LEN(@String)-1,-1))|code-block|syntax|javascript|1481868|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try It : 

<pre><code> DECLARE @String NVARCHAR(100)
 SET @String = '12354851'
 SELECT LEFT(@String, NULLIF(LEN(@String)-1,-1))
</code></pre>

blocks|key|1481883|text|declare+@string+varchar(20)=+'TEST+STRING'
Select+left(@string,+len(@string)-1)+as+Tada|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1481884|输出：|unstyled|1481885|Tada
--------------------
TEST+STRIN|1481886|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>declare @string varchar(20)= 'TEST STRING'
Select left(@string, len(@string)-1) as Tada
</code></pre>

output:

<pre><code>Tada
--------------------
TEST STRIN
</code></pre>

blocks|key|1267252|text|select+left('TEST+STRING',+len('TEST+STRING')-1)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1267253|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select left('TEST STRING', len('TEST STRING')-1)
</code></pre>

blocks|key|1481908|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1481909|DECLARE+@String+VARCHAR(100)
SET+@String+=+'TEST+STRING'
SELECT+LEFT(@String,+LEN(@String)+-+1)+AS+MyTrimmedColumn|code-block|syntax|javascript|1481910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this

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

blocks|key|1267308|text|尝尝这个,|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267309|DECLARE+@name+NVARCHAR(MAX)+SET+@name='xxxxTHAMIZHMANI****'SELECT+Substring(@name,+5,+(len(@name)-8))+as+UserNames|code-block|syntax|javascript|1267310|输出结果如下:+THAMIZHMANI|1267311|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this,

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

And the output will be like, THAMIZHMANI

blocks|key|1267326|text|@result+=+substring(@result,+1,+(LEN(@result)-1))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1267327|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>@result = substring(@result, 1, (LEN(@result)-1))
</code></pre>

blocks|key|1267354|text|即使在source+text/var为null或为空的情况下，这也会起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267355|SELECT+REVERSE(SUBSTRING(REVERSE(@a),+2,+9999))|code-block|syntax|javascript|1267356|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will work even when source text/var is null or empty:
<pre><code>SELECT REVERSE(SUBSTRING(REVERSE(@a), 2, 9999))
</code></pre>

blocks|key|1267392|text|这是相当晚的，但有趣的是，还没有提到。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1267393|select+stuff(x,len(x),1,'')|code-block|syntax|javascript|1267394|即：|1267395|take+a+string+x
go+to+its+last+character
remove+one+character
add+nothing|1267396|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This is quite late, but interestingly never mentioned yet.
<pre><code>select stuff(x,len(x),1,'')
</code></pre>
ie:
<pre><code>take a string x
go to its last character
remove one character
add nothing
</code></pre>

How do I remove the last character in a string in <code>T-SQL</code>?

For example:

<pre><code>'TEST STRING'
</code></pre>

to return:

<pre><code>'TEST STRIN'
</code></pre>

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

如何在T-SQL中删除字符串中的最后一个字符例如：'TEST STRING'返回：'TEST STRIN'

问在T-SQL中删除字符串中的最后一个字符？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在T-SQL中删除字符串中的最后一个字符？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在T-SQL中删除字符串中的最后一个字符？
EN