如何删除 SQL Server 中的前导和尾随引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1066134/
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 can I remove leading and trailing quotes in SQL Server?
提问by adeel825
I have a table in a SQL Server database with an NTEXT column. This column may contain data that is enclosed with double quotes. When I query for this column, I want to remove these leading and trailing quotes.
我在 SQL Server 数据库中有一个带有 NTEXT 列的表。此列可能包含用双引号括起来的数据。当我查询此列时，我想删除这些前导和尾随引号。
For example:
例如：
"this is a test message"
“这是一条测试消息”
should become
应该成为
this is a test message
这是一条测试消息
I know of the LTRIM and RTRIM functions but these workl only for spaces. Any suggestions on which functions I can use to achieve this.
我知道 LTRIM 和 RTRIM 函数，但这些仅适用于空格。关于我可以使用哪些功能来实现这一目标的任何建议。
回答by WowtaH
I have just tested this code in MS SQL 2008 and validated it.
我刚刚在 MS SQL 2008 中测试了这段代码并对其进行了验证。
Remove left-most quote:
删除最左边的引用：
UPDATE MyTable
SET FieldName = SUBSTRING(FieldName, 2, LEN(FieldName))
WHERE LEFT(FieldName, 1) = '"'
Remove right-most quote: (Revised to avoid error from implicit type conversion to int)
删除最右边的引号：（已修改以避免从隐式类型转换为 int 的错误）
UPDATE MyTable
SET FieldName = SUBSTRING(FieldName, 1, LEN(FieldName)-1)
WHERE RIGHT(FieldName, 1) = '"'
回答by Mark O'Grady
I thought this is a simpler script if you want to remove all quotes
如果您想删除所有引号，我认为这是一个更简单的脚本
UPDATE Table_Name
SET col_name = REPLACE(col_name, '"', '')
回答by user2043286
I know this is an older question post, but my daughter came to me with the question, and referenced this page as having possible answers. Given that she's hunting an answer for this, it's a safe assumption others might still be as well.
我知道这是一个较旧的问题帖子，但我女儿带着问题来找我，并引用此页面作为可能的答案。鉴于她正在为此寻找答案，这是一个安全的假设，其他人可能仍然如此。
All are great approaches, and as with everything there's about as many way to skin a cat as there are cats to skin.
所有都是很好的方法，就像所有的东西一样，给猫剥皮的方法和有猫去皮的方法一样多。
If you're looking for a left trim anda right trim of a character or string, andyour trailing character/string is uniform in length, here's my suggestion:
如果您正在寻找字符或字符串的左修剪和右修剪，并且您的尾随字符/字符串的长度是统一的，那么我的建议是：
SELECT SUBSTRING(ColName,VAR, LEN(ColName)-VAR)
Or in this question...
或者在这个问题...
SELECT SUBSTRING('"this is a test message"',2, LEN('"this is a test message"')-2)
With this, you simply adjust the SUBSTRING starting point (2), and LEN position (-2) to whatever value you need to remove from your string.
有了这个，您只需将 SUBSTRING 起点 (2) 和 LEN 位置 (-2) 调整为您需要从字符串中删除的任何值。
It's non-iterative and doesn't require explicit case testing and above all it's inlineall of which make for a cleaner execution plan.
它是非迭代的，不需要明确的案例测试，最重要的是它是内联的，所有这些都有助于制定更清晰的执行计划。
回答by Deadman
You can simply use the "Replace" function in SQL Server.
您可以简单地使用 SQL Server 中的“替换”功能。
like this ::
像这样 ：：
select REPLACE('this is a test message','"','')
note: second parameter here is "double quotes" inside two single quotes and third parameter is simply a combination of two single quotes. The idea here is to replace the double quotes with a blank.
注意：这里的第二个参数是两个单引号内的“双引号”，第三个参数只是两个单引号的组合。这里的想法是用空格替换双引号。
Very simple and easy to execute !
非常简单且易于执行！
回答by Dave Sexton
My solution is to use the difference in the the column values length compared the same column length but with the double quotes replaced with spaces and trimmed in order to calculate the start and length values as parameters in a SUBSTRING function.
我的解决方案是使用列值长度的差异与相同的列长度进行比较，但将双引号替换为空格并进行修剪，以便计算作为 SUBSTRING 函数中的参数的起始值和长度值。
The advantage of doing it this way is that you can remove any leading or trailing character even if it occurs multiple times whilst leaving any characters that are contained within the text.
这样做的好处是您可以删除任何前导或尾随字符，即使它出现多次，同时保留文本中包含的任何字符。
Here is my answer with some test data:
这是我的一些测试数据的答案：
SELECT
x AS before
,SUBSTRING(x
,LEN(x) - (LEN(LTRIM(REPLACE(x, '"', ' ')) + '|') - 1) + 1 --start_pos
,LEN(LTRIM(REPLACE(x, '"', ' '))) --length
) AS after
FROM
(
SELECT 'test' AS x UNION ALL
SELECT '"' AS x UNION ALL
SELECT '"test' AS x UNION ALL
SELECT 'test"' AS x UNION ALL
SELECT '"test"' AS x UNION ALL
SELECT '""test' AS x UNION ALL
SELECT 'test""' AS x UNION ALL
SELECT '""test""' AS x UNION ALL
SELECT '"te"st"' AS x UNION ALL
SELECT 'te"st' AS x
) a
Which produces the following results:
这会产生以下结果：
before after
-----------------
test test
"
"test test
test" test
"test" test
""test test
test"" test
""test"" test
"te"st" te"st
te"st te"st
One thing to note that when getting the length I only need to use LTRIM and not LTRIM and RTRIM combined, this is because the LEN function does not count trailing spaces.
需要注意的一件事是，在获取长度时我只需要使用 LTRIM 而不是 LTRIM 和 RTRIM 组合，这是因为 LEN 函数不计算尾随空格。
回答by DaveN
Some UDFs for re-usability.
一些用于可重用性的 UDF。
Left Trimming by character (any number)
按字符左修剪（任意数字）
CREATE FUNCTION [dbo].[LTRIMCHAR] (@Input NVARCHAR(max), @TrimChar CHAR(1) = ',')
RETURNS NVARCHAR(max)
AS
BEGIN
RETURN REPLACE(REPLACE(LTRIM(REPLACE(REPLACE(@Input,' ','|'), @TrimChar, ' ')), ' ', @TrimChar),'|',' ')
END
Right Trimming by character (any number)
按字符右修剪（任意数字）
CREATE FUNCTION [dbo].[RTRIMCHAR] (@Input NVARCHAR(max), @TrimChar CHAR(1) = ',')
RETURNS NVARCHAR(max)
AS
BEGIN
RETURN REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(@Input,' ','|'), @TrimChar, ' ')), ' ', @TrimChar),'|',' ')
END
Note the dummy character '|' (Alt+0166) cannot be present in the data (you may wish to test your input string, first, if unsure or use a different character).
注意虚拟字符“|” (Alt+0166) 不能出现在数据中（您可能希望首先测试您的输入字符串，如果不确定或使用不同的字符）。
回答by Germstorm
The following script removes quotation marks only from around the column value if table is called [Messages] and the column is called [Description].
如果表名为 [Messages] 且列名为 [Description]，则以下脚本仅删除列值周围的引号。
-- If the content is in the form of "anything" (LIKE '"%"')
-- Then take the whole text without the first and last characters
-- (from the 2nd character and the LEN([Description]) - 2th character)
UPDATE [Messages]
SET [Description] = SUBSTRING([Description], 2, LEN([Description]) - 2)
WHERE [Description] LIKE '"%"'
回答by Scott Ivey
you could replace the quotes with an empty string...
您可以用空字符串替换引号...
SELECT AllRemoved = REPLACE(CAST(MyColumn AS varchar(max)), '"', ''),
LeadingAndTrailingRemoved = CASE
WHEN MyTest like '"%"' THEN SUBSTRING(Mytest, 2, LEN(CAST(MyTest AS nvarchar(max)))-2)
ELSE MyTest
END
FROM MyTable
回答by ShaileshDev
You can use following query which worked for me-
您可以使用以下对我有用的查询-
For updating-
对于更新-
UPDATE table SET colName= REPLACE(LTRIM(RTRIM(REPLACE(colName, '"', ''))), '', '"') WHERE...
For selecting-
用于选择-
SELECT REPLACE(LTRIM(RTRIM(REPLACE(colName, '"', ''))), '', '"') FROM TableName
回答by Nacho Fregeiro
You can use TRIM('"' FROM '"this "is" a test"')which returns: this "is" a test
您可以使用TRIM('"' FROM '"this "is" a test"')which 返回：this "is" a test
