SQL 如何替换sql server中列的第一个和最后一个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22352610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace first and last character of column in sql server?
提问by Manish Sharma
I have a database column and its give a string like ,Recovery, Pump Exchange,.
我有一个数据库列，它给出一个像,Recovery, Pump Exchange,.
I want remove first and last comma from string.
我想从字符串中删除第一个和最后一个逗号。
Expected Result : Recovery, Pump Exchange.
预期结果：Recovery, Pump Exchange。
回答by dasblinkenlight
You can use SUBSTRINGfor that:
你可以使用SUBSTRING：
SELECT
SUBSTRING(col, 2, LEN(col)-2)
FROM ...
Obviously, an even better approach would be not to put leading and trailing commas there in the first place, if this is an option.
显然，如果这是一个选项，更好的方法是不首先将前导和尾随逗号放在那里。
I want to remove last and first comma only if exist otherwise not.
我只想删除最后一个和第一个逗号，否则不存在。
The expression becomes a little more complex, but the idea remains the same:
表达式变得有点复杂，但思想保持不变：
SELECT SUBSTRING(
col
, CASE LEFT(@col,1) WHEN ',' THEN 2 ELSE 1 END
, LEN(@col) -- Start with the full length
-- Subtract 1 for comma on the left
- CASE LEFT(@col,1) WHEN ',' THEN 1 ELSE 0 END
-- Subtract 1 for comma on the right
- CASE RIGHT(@col,1) WHEN ',' THEN 1 ELSE 0 END
)
FROM ...
回答by bendataclear
Alternatively to dasblinkenlight's method you could use replace:
替代 dasblinkenlight 的方法，您可以使用替换：
DECLARE @words VARCHAR(50) = ',Recovery, Pump Exchange,'
SELECT REPLACE(','+ @words + ',',',,','')
回答by gbn
Using LEN could backfire because LEN ignores trailing spaces. These could be added because of ANSI_PADDING defaulting to ON. So, you'd need RTRIM.
使用 LEN 可能会适得其反，因为 LEN 会忽略尾随空格。由于 ANSI_PADDING 默认为 ON，因此可以添加这些。所以，你需要 RTRIM。
For completeness, I've added LTRIM too...
为了完整起见，我也添加了 LTRIM ...
REVERSE(SUBSTRING(REVERSE(RTRIM(LTRIM(SUBSTRING(MyCol, 2, 8000)))), 2, 8000))
回答by Raging Bull
Use Substring():
使用Substring()：
SET @String=SUBSTRING(@String ,2,Len(@String)-2)
SUBSTRING()returns part of an expression.
SUBSTRING()返回表达式的一部分。
Syntax:
句法：
SUBSTRING ( expression ,start , length )
回答by Rahul Tripathi
Try like this:
像这样尝试：
if(Substring(@mykeyword, 1,1) = ',' and Substring(@mykeyword, LEN(@mykeyword) - 1, LEN(@mykeyword))=',')
SET @mykeyword = Substring(@mykeyword, 2, LEN(@mykeyword) - 2)
回答by TaBi
SELECT LEFT(RIGHT(',Recovery, Pump Exchange,',LEN(',Recovery, Pump Exchange,')-1),LEN(',Recovery, Pump Exchange,')-2)
回答by Ragul
For removing same character you can use below system function TRIM
要删除相同的字符，您可以使用以下系统功能TRIM
Only from 2017
仅来自 2017
DECLARE @str VARCHAR(MAX) = '[REMOVE THIS CLOSE BRACKET]'
SELECT TRIM('[]' FROM @str)
But for different character removing you need to use SUBSTRING
但是对于不同的字符删除，您需要使用 SUBSTRING
SELECT SUBSTRING(@str,2,LEN(@str)-2)
回答by Amélie Dupré
This is an old post but I've decided to add my solution as it will remove the first and last commas of a comma separated string without removing the other commas and also will work with strings that don't start with a comma:
这是一篇旧帖子，但我决定添加我的解决方案，因为它将删除逗号分隔字符串的第一个和最后一个逗号，而不删除其他逗号，并且还可以处理不以逗号开头的字符串：
DECLARE @words VARCHAR(50) = ',Recovery, Pump Exchange,'
SELECT REPLACE(SUBSTRING(@words , 1, 1),',','') + SUBSTRING(@words, 2, LEN(@words)-2) + REPLACE(SUBSTRING(@words, LEN(@words), 1), ',', '')
