SQL 如何从字符串中删除特定字符，仅当它是字符串中的第一个或最后一个字符时。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20696635/
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 remove a specific character from a string, only when it is the first or last character in the string.
提问by SamuraiHyman
Suppose I have a string 1,2,3,I would like to remove the last ,or if the string looks like ,1,2,3,or ,1,2,3I would still like to get 1,2,3as my result.
And please try to be a little explanatory in your answer. I do not just want to copy paste stuff without understanding it. Thank you.
假设我有一个字符串，1,2,3,我想删除最后一个字符串，,或者如果字符串看起来像，,1,2,3,或者 ,1,2,3我仍然想得到1,2,3我的结果。请尽量在你的回答中做一些解释。我不只是想在不理解的情况下复制粘贴内容。谢谢你。
回答by dasblinkenlight
One way to deal with "trimming" commas like that would be using a CASEstatement:
处理这样的“修剪”逗号的一种方法是使用CASE语句：
CASE
WHEN str LIKE ',%,' THEN SUBSTRING(str, 2, LEN(str)-2)
WHEN str LIKE ',%' THEN RIGHT(str, LEN(str)-1)
WHEN str LIKE '%,' THEN LEFT(str, LEN(str)-1)
ELSE str
END
This is very much self-explanatory: the CASEstatement considers three situations -
这是非常不言自明的：该CASE声明考虑了三种情况-
- When the string
strhas commas on both sides, - When the string
strstarts in a comma, but does not end in one, and - When the string
strends in a comma, but does not start in one.
- 当字符串
str两边都有逗号时， - 当字符串
str以逗号开头但不以一个结尾时，并且 - 当字符串
str以逗号结尾，但不是以一个开头时。
In the first case, the first and the last characters are removed; in the second case, the leftmost character is removed; in the last case, the trailing character is removed.
第一种情况，去掉第一个和最后一个字符；在第二种情况下，最左边的字符被删除；在最后一种情况下，尾随字符被删除。
回答by Poonam
declare @str varchar(20)=',1,2,3,'
select case
when @str like ',%,' then stuff(stuff(@str, 1, 1, ''),LEN(stuff(@str, 1, 1, '')),1,'')
when @str like ',%' then stuff(@str, 1, 1, '')
when @str like '%,' then stuff(@str, LEN(@str), 1, '')
else @str
end
回答by valex
If you need to replace just one comma (as it is in your original question) in the start or in the end then:
如果您只需要在开头或结尾替换一个逗号（就像在您的原始问题中一样），则：
CASE
WHEN str LIKE '%,%' THEN
SUBSTRING(str, IIF(LEFT(str,1)=',',2,1),
LEN(str)-IIF(LEFT(str,1)=',',1,0)
-IIF(RIGHT(str,1)=',',1,0))
ELSE str
END
If ,,,,1,2,3,,,,is possible then use PATINDEX()function with '%[0-9]%mask:
如果,,,,1,2,3,,,,可能，则使用带有掩码的PATINDEX()函数'%[0-9]%：
CASE
WHEN str LIKE '%,%' THEN
SUBSTRING(str, PATINDEX('%[0-9]%',str),
LEN(str)-(PATINDEX('%[0-9]%',str)-1)
-(PATINDEX('%[0-9]%',REVERSE(str))-1))
ELSE str
END
回答by Solomon Rutzky
A few options, all using SQLCLR, if that is something that you are not denied access to. You can write your own functions to do these things but the examples I will show are based on the SQL# (SQLsharp)library. I am the author of the SQL# library, but the first 2 examples are using functions available in the Free version. The 3rd example, using TrimChars(), is not Free but I included it for completeness since it was just so simple ;-). In each example, the characters inside of the pre or post extra commas can be anything.
一些选项，全部使用 SQLCLR，如果您不会拒绝访问。您可以编写自己的函数来执行这些操作，但我将展示的示例基于SQL# (SQLsharp)库。我是 SQL# 库的作者，但前两个示例使用的是免费版本中可用的函数。第三个例子，使用 TrimChars()，不是免费的，但为了完整性我把它包括在内，因为它太简单了 ;-)。在每个示例中，前置或后置额外逗号内的字符可以是任何内容。
A somewhat simple Regular Expression for use in a Capture Group to get everything from the first non-comma value through the last non-comma value:
SELECT SQL#.RegEx_CaptureGroup(N',,12,123,12334,567,,,,,', N'^,*(.+?),*$', -- Regular Expression 1, -- Capture Group to return '', -- not found replacement 1, -- Start At position -1, -- Length '' -- RegEx Options )If the reason for removing the extra commas is to get a clean list for splitting, you can split them and have the split function remove the empty values for you and hence not need to worry about where empty values are, even if in the middle:
SELECT *FROM SQL#.String_Split(N',,12,123,12334,567,,,,,', N',', 2) -- 2 = remove empty valuesYou can do a simple Trim that removes the specified character from both sides until it finds a character that is not the character to Trim:
SELECT SQL#.String_TrimChars(N',,12,123,12334,567,,,,,', N',')
一个在捕获组中使用的有点简单的正则表达式，用于获取从第一个非逗号值到最后一个非逗号值的所有内容：
SELECT SQL#.RegEx_CaptureGroup(N',,12,123,12334,567,,,,,', N'^,*(.+?),*$', -- Regular Expression 1, -- Capture Group to return '', -- not found replacement 1, -- Start At position -1, -- Length '' -- RegEx Options )如果删除多余逗号的原因是为了获得一个干净的拆分列表，您可以拆分它们并让 split 函数为您删除空值，因此无需担心空值在哪里，即使在中间：
SELECT *FROM SQL#.String_Split(N',,12,123,12334,567,,,,,', N',', 2) -- 2 = remove empty values你可以做一个简单的修剪，从两边删除指定的字符，直到找到一个不是要修剪的字符：
SELECT SQL#.String_TrimChars(N',,12,123,12334,567,,,,,', N',')
回答by Ben82
If you just want to remove a trailing comma, in SQL Server, I'd use the STUFF function, and it would look something like this:
如果您只想删除尾随逗号，在 SQL Server 中，我会使用 STUFF 函数，它看起来像这样：
SELECT STRING, stuff(string, (len(string) - 1),1,'') as newstring
from yourtable
回答by jneal
Try this. REVERSE(SUBSTRING(REVERSE(fld),2,LEN(fld))) It will always take of the last character regardless. You can put a CASE WHEN statement to ensure the last character is comma by simply saying CASE WHEN REVERSE(SUBSTRING(fld,1,1))=',' THEN REVERSE(SUBSTRING(REVERSE(fld),2,LEN(fld))) ELSE fld END fld
尝试这个。REVERSE(SUBSTRING(REVERSE(fld),2,LEN(fld))) 不管怎样，它总是取最后一个字符。您可以放置一个 CASE WHEN 语句来确保最后一个字符是逗号，只需说 CASE WHEN REVERSE(SUBSTRING(fld,1,1))=',' THEN REVERSE(SUBSTRING(REVERSE(fld),2,LEN(fld) )) ELSE fld END fld
回答by Poonam
dept_nameis column name and 'I' is a character you want to replace.
dept_name是列名，'I' 是要替换的字符。
SELECT REPLACE(Dept_Name,'I','')
FROM [Newone].[dbo].[Department]
where Dept_Name like 'I%' or Dept_Name like '%I'
回答by shadowjfaith
CASEwould work here. I'm thinking something like this:
CASE会在这里工作。我在想这样的事情：
CASE
WHEN ISNUMERIC(RIGHT(YourString, 1)) <> 1 AND ISNUMERIC(LEFT(YourString, 1)) <> 1
THEN SELECT MID(YourString, 2, LEN(YourString) - 2)
WHEN ISNUMERIC(RIGHT(YourString, 1)) <> 1
THEN SELECT LEFT(YourString, LEN(YourString) -1)
WHEN ISNUMERIC(LEFT(YourString, 1)) <> 1
THEN SELECT RIGHT(YourString, LEN(YourString) - 1)
ELSE SELECT YourString
END
This works no matter what the character is as it checks to see if the first and last are numeric first, if not get your new string, else check to see if the end is numeric if not select the string besides the end, else check to see if the beginning is numeric if not select the string besides the beginning, else get your string.
无论字符是什么，这都有效，因为它首先检查第一个和最后一个是否为数字，如果没有得到新字符串，否则检查结尾是否为数字，如果没有选择结尾以外的字符串，否则检查查看开头是否为数字，如果不选择开头以外的字符串，则获取您的字符串。
